We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Inner Join Column Aliases... how

    • 40641 ☆ A M B ☆
    • 56 Posts
    chrischerrett Reply #1, 13 years, 10 months ago 
    $c = $modx->newQuery('modDiplomaCourse');
$c->innerJoin('modDiploma', 'D', array("modDiplomaCourse.diploma_id = D.id"));
$c->innerJoin('modCourse', 'C', array("modDiplomaCourse.course_id = C.id"));
$c->select(array('modDiplomaCourse.*, D.reference'));
$c->select(array('modDiplomaCourse.*, C.reference'));
$diplomascourses= $modx->getCollection('modDiplomaCourse', $c);


    I have this query, and I'm trying to get the references from the joined tables. The column is called 'reference' in both tables. My problem is that C.reference overrides D.Reference in the resulting array:

    Array ( [id] => 3 [diploma_id] => 1 [course_id] => 90 [reference] => CRS-003 )


    If I could create an alias for each column, that would solve my problem (reference_diploma, reference_course). But how do I do it?
      • 8386 ☆ A M B ☆
      • 160 Posts
      Silent Works Reply #2, 13 years, 10 months ago
      How about trying this
      $c = $modx->newQuery('modDiplomaCourse');
$c->innerJoin('modDiploma', 'D', array("modDiplomaCourse.diploma_id = D.id"));
$c->innerJoin('modCourse', 'C', array("modDiplomaCourse.course_id = C.id"));
$c->select('modDiplomaCourse.*, D.reference AS d_reference');
$c->select('modDiplomaCourse.*, C.reference');
$diplomascourses= $modx->getCollection('modDiplomaCourse', $c);
        • 40641 ☆ A M B ☆
        • 56 Posts
        chrischerrett Reply #3, 13 years, 10 months ago
        Sorry, I see what I was doing wrong now. I had misunderstood the role of $c->select

        This worked:

        $c = $modx->newQuery('modDiplomaCourse');
$c->innerJoin('modDiploma', 'D', array("modDiplomaCourse.diploma_id = D.id"));
$c->innerJoin('modCourse', 'C', array("modDiplomaCourse.course_id = C.id"));
$c->select(array('modDiplomaCourse.*, D.reference as `ref1`, C.reference as `ref2`'));
          • 4172
          • 5,888 Posts
          Bruno17 Reply #4, 13 years, 10 months ago
          you can prefix all fields with

          $c = $modx->newQuery('modDiplomaCourse');
$c->innerJoin('modDiploma', 'Diploma', array("modDiplomaCourse.diploma_id = Diploma.id"));
$c->innerJoin('modCourse', 'Course', array("modDiplomaCourse.course_id = Course.id"));
$c->select($modx->getSelectColumns('modDiploma','Diploma','Diploma_'));
$c->select($modx->getSelectColumns('modCourse','Course','Course_'));
            -------------------------------

            you can buy me a beer, if you like MIGX

            http://webcmsolutions.de/migx.html

            Thanks!