Is there a way to leftJoin a subquery?

93 Posts

Jeff Miranda Reply #1, 13 years, 4 months ago

Hi everybody! I'm trying to pull some data into my MODExt grid for my CMP and the SQL statement I need converted to xPDO is:

SELECT * FROM sc_student_assignments
LEFT JOIN
(SELECT
  student_assignment_id,
  user_id,
  comment,
  MAX(date_created) AS `comment_date`
FROM `sc_assignment_comments`
GROUP BY student_assignment_id) AS latest_comments
ON
sc_student_assignments.id = latest_comments.student_assignment_id;

The above statement works fine but for the life of me I can't figure out how to convert it to xPDO for use in my processor. Any help would be much appreciated! Thanks!

☆ A M B ☆
2,475 Posts

Everett Reply #2, 13 years, 4 months ago

http://rtfm.modx.com/display/xPDO20/xPDOQuery.leftJoin

For what it's worth, if your queries get too tricky, it might be worth it to just run the query without trying to abstract it.

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

93 Posts

Jeff Miranda Reply #3, 13 years, 4 months ago

Thanks Everett! I was pouring over the documentation and came across that as well. I tried to pass an xPDOQuery object as a fourth parameter:

public function prepareQueryBeforeCount(xPDOQuery $c) {
    $subquery = $xpdo->newQuery('scComment');
    $subquery->select(array('
        student_assignment_id,
        user_id,
        comment,
        MAX(date_created) AS `comment_date`
    '));
    $subquery->groupby('student_assignment_id');
    $c->leftJoin('scComment', 'LatestComments', array (
        'scStudentAssignment.id = LatestComments.student_assignment_id'
    ), $subquery);
    return $c
}

But it didn't work. If I were to simply run the query without abstraction, how would I do that within a processor when you need to return an xPDOQuery object? [ed. note: jeffmiranda last edited this post 13 years, 4 months ago.]

☆ A M B ☆
2,475 Posts

Everett Reply #4, 13 years, 4 months ago

I meant the query method: http://rtfm.modx.com/display/xPDO20/xPDO.query

Returning an xPDOQuery object... I think you can still do that.

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

☆ A M B ☆
427 Posts

W. Shawn Wilkerson Reply #5, 13 years, 4 months ago

I would look into getObjectGraph, getCollectionGraph

Btw, your code is flawed:

public function prepareQueryBeforeCount(xPDOQuery $c) {
    $subquery = $xpdo->newQuery('scComment');
    $subquery->select(array('
        student_assignment_id,
        user_id,
        comment,
        MAX(date_created) AS `comment_date`
    '));
    $subquery->groupby('student_assignment_id');
    $subquery->innerJoin('scComment', 'LatestComments', array (
        'scStudentAssignment.id = LatestComments.student_assignment_id'
    ));
    return $subquery;
}

See if that will work. I changed the $c from the docs to $subquery to match your newQuery Object.

Get your copy of MODX Revolution Building the Web Your Way http://www.sanitypress.com/books/modx-revolution-building-the-web-your-way.html

Check out my MODX || xPDO resources here: http://www.shawnwilkerson.com

93 Posts

Jeff Miranda Reply #6, 13 years, 4 months ago

Thanks for that Shawn! I was on the road when I responded and was quickly trying to do the function from memory. I've made the corrections to the function in my post above.

getObjectGraph and getCollectionGraph would work great but isn't the function prepareQueryBeforeCount expected to return an xPDOQuery object? Is there really no way to leftJoin a subquery on an xPDOQuery object AND return that xPDOQuery object back to the processor?