FuelPHP Forums

selenium09

Hi.
The problem is that if "father" is not found, I'm getting empty $data: var_dump($data) returns array(0) { }.
I need to get all $data that is found with only $data->father = array(0) { }.

Same for kids. If father, mother and at least one kid is found it's ok, but if there are no kids - empty $data.

What am I doing wrong? I'm using 1.5.2.

$data = Model_Record::query()
                ->where('user_id', $this->user_id)
                ->related('father')
                ->related('mother')
                ->related('kids')
                ->where('father.user_id','=',$this->user_id)
                ->where('mother.user_id','=',$this->user_id)
                ->where('kids.user_id','=',$this->user_id)
                ->offset($pagination->offset)
                ->limit($pagination->per_page)
                ->get();

Table "record" has fields:
id
father_id
mother_id
notes
deleted

Here is Model_Record:
class Model_Record extends Orm\Model {

protected static $_table_name = 'record';

    protected static $_conditions = array(
        'where' => array('deleted' => '0'),
        'order_by' => array('id' => 'desc'),
    );

    protected static $_has_many = array(
        'kids' => array(
            'model_to' => 'Model_Members',
            'key_from' => 'id',
            'key_to' => 'record_id',
            'cascade_save' => true,
            'cascade_delete' => false,
        )
    );
    protected static $_has_one = array(
        'mother' => array(
            'key_from' => 'mother_id',
            'model_to' => 'Model_Members',
            'key_to' => 'id',
            'cascade_save' => true,
            'cascade_delete' => false,
        ),
        'father' => array(
            'key_from' => 'father_id',
            'model_to' => 'Model_Members',
            'key_to' => 'id',
            'cascade_save' => true,
            'cascade_delete' => false,
            ));
}

Harro

By default the relations are added to the query using an INNER JOIN, which means the relation must exist.

Check out http://docs.fuelphp.com/packages/orm/relations/intro.html, "Join types" for alternative ways to construct the query.

selenium09

Tryed it:
$data = Model_Record::find('all', array(
                    'where' => array(
                        array('user_id', '=', $this->user_id)
                    ),
                    'related' => array(
                        'father' => array(
                            'join_type' => 'left',
                            'join_on' => array(
                                array('user_id', '=', $this->user_id),
                            ),
                        ),'mother' => array(
                            'join_type' => 'left',
                            'join_on' => array(
                                array('user_id', '=', $this->user_id),
                            ),
                        ),'kids' => array(
                            'join_type' => 'left',
                            'join_on' => array(
                                array('user_id', '=', $this->user_id),
                            ),
                        ),
                    ),
                ));

It returned totally everithing, even what it shouldn't have - а kid with same parents, but different user_id.

Harro

Problem is that ORM's are about relations, not about things that don't have a relation.

Perhaps you should dump the query generated, see if you can modify it to produce the output you want, and then work your way back.

But I think it's going to be complicated creating a single query with all these joins, which may or may not result in data.

selenium09

How do I dump the generated query using "find"? It seems like ORM ignores "join_on" conditions.

Got an idea - is it possible to do something like this in models?

public function __construct() {
    $reflection = new \ReflectionProperty('Model_Record', '_conditions');
    $reflection->setAccessible(true);
    $reflection->setValue(null, array(
        'where' => array(
            array('deleted' => '0'),
            array('user_id','=', Session::get('user_id'))
            ),
        'order_by' => array('id' => 'desc')));
    }

This exact variant doesn't work properly, is there any similar way that does?

Harro

You can dump the query either by activating the profiler (in your config and db config files), or use DB::last_query() directly after the find.

Using reflection should not be needed, if it ignores the conditions it should be fixed. Which version of the framework (and the ORM) are you using?

selenium09

I'm using Fuel 1.5.2. So the version of ORM is 1.5.2 too?
var_dump(DB::last_query()) returned:

"SELECT `t0`.`id` AS `t0_c0`, `t0`.`father_id` AS `t0_c1`, `t0`.`mother_id` AS `t0_c2`, `t0`.`notes` AS `t0_c3`, `t0`.`user_id` AS `t0_c4`, `t0`.`deleted` AS `t0_c6`, `t1`.`id` AS `t1_c0`, `t1`.`name` AS `t1_c1`, `t1`.`record_id` AS `t1_c2`, `t1`.`father_id` AS `t1_c3`, `t1`.`mother_id` AS `t1_c4`, `t1`.`gender` AS `t1_c5`, `t1`.`birthday` AS `t1_c6`, `t1`.`notes` AS `t1_c7`, `t1`.`user_id` AS `t1_c8`, `t1`.`deleted` AS `t1_c9`, `t2`.`id` AS `t2_c0`, `t2`.`name` AS `t2_c1`, `t2`.`record_id` AS `t2_c2`, `t2`.`father_id` AS `t2_c3`, `t2`.`mother_id` AS `t2_c4`, `t2`.`gender` AS `t2_c5`, `t2`.`birthday` AS `t2_c6`, `t2`.`notes` AS `t2_c7`, `t2`.`user_id` AS `t2_c8`, `t2`.`deleted` AS `t2_c9`, `t3`.`id` AS `t3_c0`, `t3`.`name` AS `t3_c1`, `t3`.`record_id` AS `t3_c2`, `t3`.`father_id` AS `t3_c3`, `t3`.`mother_id` AS `t3_c4`, `t3`.`gender` AS `t3_c5`, `t3`.`birthday` AS `t3_c6`,`t3`.`notes` AS `t3_c7`, `t3`.`user_id` AS `t3_c8`, `t3`.`deleted` AS `t3_c9` FROM `record` AS `t0` LEFT JOIN `members` AS `t1` ON (`t0`.`father_id` = `t1`.`id`) LEFT JOIN `members` AS `t2` ON (`t0`.`mother_id` = `t2`.`id`) LEFT JOIN `members` AS `t3` ON (`t0`.`id` = `t3`.`record_id`) WHERE `t0`.`user_id` = 4 AND `t0`.`deleted` = '0' ORDER BY `t0`.`id` DESC"

Harro

The ORM is a separate package. If you use the zip file, everything installed is the same version, but if you use a cloned repo, you can update each FuelPHP component individually.

The generated query looks correct to me for the question you're asking. You're doing a LEFT JOIN here on all relations, which is an OUTER JOIN, which should return partial results without problems.

What happens if you paste this directly into the mysql commandline (or a tool like phpmyadmin)?

selenium09

In the end someone explained to me that what I want is impossible.

Thank you for trying to help.

Still is it ok that something like this is not in DB::last_query() result?

...WHERE `t0`.`user_id` = 4 AND `t1`.`user_id` = 4 AND `t2`.`user_id` = 4 AND `t3`.`user_id` = 4 AND `t0`.`deleted` = '0' AND `t1`.`deleted` = '0' AND `t2`.`deleted` = '0' AND `t3`.`deleted` = '0' ORDER BY `t0`.`id` DESC

Harro

An ORM links parent and child objects by defining a relation between the two, and specifying the FK used to make the relation. The FK is always linked to the PK on the other side of the relation.

You define the relation as Model_Record has_one Model_Members using father_id, which is wrong, because the record that holds the FK most have a belongs_to to it's parent. Same for mother. I think kids is defined ok, assuming that the other side of the relation is defined as belongs_to.

Looking back at your first post, all those where clauses should not be there. Only select the Model_Record you want, the ORM will make sure only related records are selected. I don't know what you mean with "user_id" where, but that doesn't look right, and could very well explain why that didn't produce any results, since you've defined conflicting where clauses.

I don't see where these 'deleted' where clauses should be generated, you haven't specified that anywhere. If you're looking for soft-delete functionality, I suggest you use Orm\Model_Soft instead, which has this feature built-in.

Harro

I don't see why such a query should not be possible, I use queries with complex relations all the time. Just verified in one of the apps I'm working on, but a query like this:

Model\Session::query()
->related('location')
->related('sessiontype')
->order_by('date', 'ASC')
->rows_offset(\Pagination::instance()->offset)
->rows_limit(\Pagination::instance()->per_page)
->get();

returns all sessions, also those that don't have a related session type or location.

selenium09

At first I did select only the Model_Record and it worked fine, but then I thought that it was a good idea to double check related data by id of the user, who added a record and all its members, so that if something was wrong, the related member was not displayed. Bad idea, what can go wrong if data is saved correctly? (or can it?) This is my first framework project ever, started it to study PHP and OOP on the go, so sometimes I end up trying to do things that are no good or impossible from the beginning.

About 'deleted' - I plan to use cron to remove these records.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion