Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
get_one/limit while searching related tables
  • 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?
  • Oh by the way I'm using 1.7-dev which I've pulled 2 days ago.
  • HarroHarro
    Accepted Answer
    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.
  • 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 :)
  • This ORM is so much cooler... ;-)

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion