FuelPHP Forums

Dear,

I have found the issue?
i am calling a belongto relation from a view, it calls the query several times as loops continue. Then how can i chain relation() query.

$a->related(array(b->related(d),c);

If you want to have nulls in the result, you need to change the join type of the relation.

You can do that in the model, in the relation definition, if you always want this behavior, or you can do that in the related() call, like I described above.

p.s. queries, or any other logic, in a view is a bad idea. The logic should be in your model or optionally in the controller, and only data should be passed to a view.

Enable the profiler in your config file, and enable database profiling in your db config.

Run your queries, then check the profiler output, it shows you the exact SQL generated, and the result in number of rows. As a double check, you could copy the generated SQL into something like PhpMySQLadmin, and see if the raw query produces any result.

If you get zero rows, your query is incorrect, if you get a result, it might be an issue with ORM hydration.

I have found some info.
LEFT join + Where = INNER join.

logically it is currect, The where will execute after left joining completed,

So i getting null if child is null after where. How should i fix that? can you recommend something.

in StackOverflow i found that using AND is recommended to get this working, how i implement AND instead of Where in ORM?

in students page, i would like to get all students of a certain institution, so i call the specific institution and all students related to it, so the page is of students, that the page shows only students, so institutions information too. if i join both, i will have to do only one query, which include all information that i need for that page,

but i have filter facility in page, by select input, when i do so, i getting null on institution if no students exist, institution always exist, bit null cause worst. whole page crashes, how i fix that, i want to do filter, i sure it is caused by LEFT + Where = Inner problem, even in phpmyadmin, it is null. kindly help

Exactly, i think you misunderstood my question, Your last answer will work, but not 100%.

what i want is , if child return null, i want it's parent should not return null.

institution always there at the ID. but sometime students not at filter. (those student who has blood group AB+ve). if so, i want the institution object, and count($institution->students) = 0.

Yes, Very Thanks, How i access IRC?

I can not reproduce your problem.

I made a complicated query, and made sure no child matched the where clauses:

$query = Model_Parent::query()
        ->where('column', 'value')
        ->and_where_open()
                ->where('id', 1)
                ->or_where('id', 2)
                ->or_where('id', 3)
                        ->or_where_open()
                                ->where('id', 99)
                        ->or_where_close()
                        ->and_where_open()
                                ->or_where('child.id', 99)
                        ->and_where_close()
                ->and_where_close()
        ->related('child')
        ->offset(0)
        ->limit(5);

print((string) $query->get_query());
var_dump($query->get());

This will generate this query (the output of the print):

SELECT `t0`.`id` AS `t0_c0`, `t0`.`name` AS `t0_c1`, `t0`.`column` AS `t0_c2`, `t1`.`id` AS `t1_c0`, `t1`.`parent_id` AS `t1_c1`, `t1`.`date` AS `t1_c2`, `t1`.`child` AS `t1_c3` FROM (SELECT `t0`.`id`, `t0`.`name`, `t0`.`column` FROM `parent` AS `t0` WHERE `t0`.`column` = 'value' LIMIT 5 OFFSET 0) AS `t0` LEFT JOIN `child` AS `t1` ON (`t0`.`id` = `t1`.`parent_id`) WHERE (`t0`.`id` = 1 OR `t0`.`id` = 2 OR `t0`.`id` = 3 OR (`t0`.`id` = 99) AND (`t1`.`id` = 99))

and If you paste this into PHPMySQLAdmin, the result is:

t0_c0     t0_c1     t0_c2     t1_c0     t1_c1     t1_c2     t1_c3
3         Name      value     NULL      NULL      NULL      NULL

Which is the parent data with zero child records. The next line dumps the result of the query, which is one parent object with the data from the parent found, and zero child records.

I don't understand your question? Both "parent" and "child" tables in my test have a single primary key, "id", and a has_many relation on "parent_id".

You don't need anything. What your primary key is follows from your database design, and the design principles you use.

If you use non-contextual PK's, like Fuel's default 'id' which is an auto-increment column, it's value has no relation to the rest of the row (although sometimes it is used as such, for example when it doubles as user id). So there is never a need for compound keys, since your auto-increment key is always unique.

But if you use contextual keys, i.e. columns that contain data, sometimes there is no single column that is guaranteed to be always unique.

See https://en.wikipedia.org/wiki/Compound_key

You can just remove the entire block between and_open() and and_close(), and the query remains the same.

If you look at what the raw query returns, the result is logical.

If there is no child matching, the result row contains NULL for all child columns. If you then add a WHERE clause saying "child.id = 1", it clearly doesn't match the value NULL, so that record is filtered.

Since there are no child records at all, all records are filtered, the result set is empty, so there is parent to hydrate from it as well.

If you accept NULL as a value, your where clause must show so:

if ($id = \Input::get('id'))
{
    $query->where_open()
        ->where('child.id', '=', $id)
        ->or_where('child.id', '=', NULL)
    ->where_close();
}

It is good practice to, if you are in a s situation with complex queries, to write your query in PHPMyAdmin in SQL first to get the proper result, and then translate it into ORM method calls.

That will also force you to learn some SQL, since I have a feeling that lack of understanding how that exactly works is the cause of the issue here...