FuelPHP Forums

mikelexp

This code...

$equipo = \Model_Equipo::query()
->related("promo")->where("promo.id", 1)
->related("colegio")->where("colegio.id", 3)
->get_one();

... produces this SQL:

SELECT
    `t0`.`id` AS `t0_c0`,
    `t0`.`promo_id` AS `t0_c1`,
    `t0`.`colegio_id` AS `t0_c2`,
    `t0`.`nombre` AS `t0_c3`,
    `t1`.`id` AS `t1_c0`,
    `t1`.`nombre` AS `t1_c1`,
    `t2`.`id` AS `t2_c0`,
    `t2`.`nombre` AS `t2_c1`,
FROM
    (
        SELECT
            `t0`.`id`,
            `t0`.`promo_id`,
            `t0`.`colegio_id`,
            `t0`.`nombre`,
        FROM
            `equipos` AS `t0`
        LIMIT 1
    ) AS `t0`
LEFT JOIN `promos` AS `t1` ON (`t0`.`promo_id` = `t1`.`id`)
LEFT JOIN `colegios` AS `t2` ON (`t0`.`colegio_id` = `t2`.`id`)
WHERE `t1`.`id` = '1'
AND `t2`.`id` = '3'

But it doesn't retrieve the right records because of where that LIMIT 1 is placed. I need it to be at the end, like so:

SELECT
    `t0`.`id` AS `t0_c0`,
    `t0`.`promo_id` AS `t0_c1`,
    `t0`.`colegio_id` AS `t0_c2`,
    `t0`.`nombre` AS `t0_c3`,
    `t1`.`id` AS `t1_c0`,
    `t1`.`nombre` AS `t1_c1`,
    `t2`.`id` AS `t2_c0`,
    `t2`.`nombre` AS `t2_c1`,
FROM
    (
        SELECT
            `t0`.`id`,
            `t0`.`promo_id`,
            `t0`.`colegio_id`,
            `t0`.`nombre`,
        FROM
            `equipos` AS `t0`
    ) AS `t0`
LEFT JOIN `promos` AS `t1` ON (`t0`.`promo_id` = `t1`.`id`)
LEFT JOIN `colegios` AS `t2` ON (`t0`.`colegio_id` = `t2`.`id`)
WHERE `t1`.`id` = '1'
AND `t2`.`id` = '3'
LIMIT 1

The relations are simple:

Equipo: belongs to Promo & Colegio
Promo: has many Equipo
Colegio: has many Equipo

What I'm trying to get is one Equipo given a certain Promo and a certain Colegio, and it doesn't work. Of course I could do this:

$equipo = \Model_Equipo::query()
->where("promo_id", 1)
->where("colegio_id", 3)
->get_one();

This works fine, but I'm worried about this being an Orm bug or something I'm not getting right.

Any ideas?

mikelexp

Oh by the way I'm using 1.7-dev which I've pulled 2 days ago.

Harro

It does produce the correct results, it only doesn't produce the results you want.

What you want is:

$equipo = \Model_Equipo::query()
->related("promo")->where("promo.id", 1)
->related("colegio")->where("colegio.id", 3)
->rows_limit(1)
->get();

which will give you the first matched result back.

The ORM will always try to maintain complete resultsets. Your query basically says "give me all promo's with id 1 and all colegio's with id 3, related to the first parent you find".

Your query retrieves incomplete results, which means that
A) $equipo->promo will be unreliable (you don't know if you have all related records or not
B) you can never make sure it does, since results are cached

This might not be a problem for retrieval only, but will give you extreme headaches when next you want to do:

$equipo = \Model_Equipo::query()
->related("promo")
->get();

of which you will expect it will return all Equipo records with all their related promo records. But this might not be the case, because the record you retrieved earlier will be re-used from the object cache, and not re-created from the new query result.

mikelexp

Harro, thank you for your excellent answer! I'm developing my first Fuel project after years of CI coding and I'm loving it, even though I still need to understand a few things better - specially with Orm, I still dream about DataMapper when I sleep :)

Harro

This ORM is so much cooler... ;-)

FuelPHP Forums

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

Howdy, Stranger!

Categories

In this Discussion