We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 33992 ☆ A M B ☆
    • 455 Posts
    Hi,
    Is it possible to limit fields of relations?
    Please consider sample below:
    $person = $xpdo->getObjectGraph('Persons', '{"Phones":{}}', 5);
    echo 'Name: '.$person->get('fullname');
    
    foreach($person->Phones as $phone)
        echo 'Phone: '.$phone->get('phonenumber');

    It executes a query like below:
    SELECT * FROM persons
    LEFT JOIN phones ON phones.person=persons.id
    WHERE persons.id=5

    Is it possible to force xPDO to use query below?
    SELECT persons.fullname , phones.phonenumber FROM persons
    LEFT JOIN phones ON phones.person=persons.id
    WHERE persons.id=5



    Thanks
    AHHP
      God loves me. 【ツ】


      MODX.ir (Persian Support)

      Boplo.ir/modx/ (Persian)
      • 31946
      • 116 Posts
      Hi. Try something like this:

      $c = $modx->newQuery('Persons');
      $c->select('Persons.id, Persons.fullname, Phones.id, Phones.phonenumber');
      $c->leftJoin('Phones','Phones');
      $c->where(array('Persons.id' => 5));
      $person = $modx->getObject('Persons',$c);
      
      echo 'Name: '.$person->get('fullname');
      echo 'Phone: '.$person->get('phonenumber');


      Not tested... leftJoin takes as third argument also the relation, for example Persons.phone_id = Phones.id
        • 22303 MODX Staff
        • 10,725 Posts
        You have to have at least the primary key (and any foreign keys involved in the relation) of each object or you can not retrieve the object. The rest of the fields can be "lazy" loaded, but be careful if you call toArray as you will trigger a new query for any unloaded field you try to retrieve.
          • 31946
          • 116 Posts
          Quote from: opengeek at Oct 30, 2011, 09:13 AM
          You have to have at least the primary key (and any foreign keys involved in the relation) of each object or you can not retrieve the object.

          So that means in my posted example the select method should look like this?
          $c->select('Persons.id, Persons.phone_id, Persons.fullname, Phones.id, Phones.phonenumber');

          Now the Primary Key's of both objects as well as the foreign key are included.
            • 33992 ☆ A M B ☆
            • 455 Posts
            I tried code below:
            $c = $xpdo->newQuery('Persons', 5);
            $c->bindGraph('{"Phones":{}}');
            $c->select('Persons.id, Persons.fullname, Phones.id, Phones.phonenumber');
            $c->prepare();
            echo $c->toSQL();

            Output is something like this:
            SELECT *, persons.id, persons.fullname , phones.id, phones.phonenumber FROM persons
            LEFT JOIN phones ON phones.person=persons.id
            WHERE persons.id=5

            So fields by xPDOQuery::select just were added to "*".
            Is there something wrong with my code?
              God loves me. 【ツ】


              MODX.ir (Persian Support)

              Boplo.ir/modx/ (Persian)
              • 22303 MODX Staff
              • 10,725 Posts
              Yes, you need to have unique prefixes that identify which class the fields belong to. id is in both tables, so you need to select them as Class1_id and Class2_id. This is what tells the graph what class the fields belong to, since it has to have fields from all of them.
                • 33992 ☆ A M B ☆
                • 455 Posts
                I tried like below but no success. Query still contains all fields + which i added via xPDOQuery::select() sad
                $c->select('Person.id AS Person_id, Person.fullname AS Person_firstname, Phone.id AS Phone_id, Phone.phonenumber AS Phone_phonenumber');

                  God loves me. 【ツ】


                  MODX.ir (Persian Support)

                  Boplo.ir/modx/ (Persian)
                  • 22303 MODX Staff
                  • 10,725 Posts
                  I would just use a join in this case and avoid the objects if you don't want all the fields. getObjectGraph is really meant for easily retrieving entire objects and automatically adds the fields to the select when the bindGraph() method is called. You can always just prepare() and execute() the resulting PDOStatement with an xPDOQuery and use that result set like any PDO result set. This also avoid object instantiation overhead in places where you don't need it.