I have a table that stores food item descriptions, and another table that stores votes for each item (think reddit-style). I want to show each food description, but sort them by how many up-votes they have.
My packages are called "substitutes" and "votes". My current query goes like this:
$itemquery = $modx->newQuery('substitutes');
$itemquery->select('substitutes.*, votes.*, COUNT(votes.vote=1) as yescount');
$itemquery->leftJoin('votes','votes', 'votes.sid = substitutes.id');
$itemquery->where(array(
'substitutes.item_name:=' => $item_name
));
// Not working yet
$itemquery->groupby('substitutes.substitute_category');
$itemquery->sortby('yescount','ASC');
$items = $modx->getCollection('substitutes',$itemquery);
This query appears to get only the items that have votes, and truncates one of them. For example, if there are 5 items total, and only 3 of them have votes, 2 will be displayed.
If I comment out the code that references the votes package (2,3,9), it displays the items as intended, just without sorting by votes. The tables have no relationship to each other. I've tried using getCollectionGraph, but yescount always came back as 0. Using getCollection gives a number when I output $item->yescount.
I don't need any information out of the votes package, I just want to sort by the row count of votes that have the same "sid" as the substitutes "id" column. How should I go about this? If there is a better way to set up my database that would make this kind of thing easier, I'm all ears.
Thank you!
