Problem with INNER JOIN

33 Posts

cruiser Reply #1, 14 years, 4 months ago

I've been spending two days on this problem and I can't figure out what is wrong.

Basically I'm trying to include colums from 2 tables in one query. I'm getting the "modAccessContext" table, and I would like to have access to the "description" column in "modContext" at the same time.

The query is quite simple:

$query = $modx->newQuery('modAccessContext');
$query->innerJoin('modContext', 'Target');

$users = $modx->getCollection('modAccessContext',$query);

foreach($users as $user) { 
  $output .= '<pre>' . print_r($user->toArray(),true);
} 

return $output;

And this is my output:

Array
(
    [id] => 1
    [target] => web
    [principal_class] => modUserGroup
    [principal] => 0
    [authority] => 9999
    [policy] => 3
)

Array
(
    [id] => 2
    [target] => mgr
    [principal_class] => modUserGroup
    [principal] => 1
    [authority] => 0
    [policy] => 2
)

Array
(
    [id] => 3
    [target] => web
    [principal_class] => modUserGroup
    [principal] => 1
    [authority] => 0
    [policy] => 2
)

Array
(
    [id] => 4
    [target] => web
    [principal_class] => modUserGroup
    [principal] => 1
    [authority] => 9999
    [policy] => 1
)

So basically all I get out is just the data from the "modAccessContext" table. It's as if it doesn't do a INNER JOIN at all. Can anyone explain what I'm doing wrong here?

863 Posts

Lucas Reply #2, 14 years, 4 months ago

You're almost there, but you also need to do a select to get the columns you need from the second table. Something like this:

$query = $modx->newQuery('modAccessContext');
$query->innerJoin('modContext', 'Target');
$query->select(array('modAccessContext.*, Target.description'));

$users = $modx->getCollection('modAccessContext',$query);
 
foreach($users as $user) { 
  $output .= '<pre>' . print_r($user->toArray(),true);
} 
 
return $output;

33 Posts

cruiser Reply #3, 14 years, 4 months ago

Thank you so much Lucas. It works like a charm now.

33 Posts

cruiser Reply #4, 14 years, 4 months ago

Got a new one for you. I'm trying to do an innerjoin on a table, that doesn't have an alias relation to the table I'm trying to join.

It goes a bit like this:

// Get list of ads from database
$query = $modx->newQuery('simpleCartOrder');
$query->innerJoin('simpleCartOrderProduct','Product');
$query->innerJoin('Adupload', 'adupload');
$query->select(array('simpleCartOrder.*, Product.*, adupload.*'));
$query->where(array(
  'simpleCartOrder.status' => 'finished',
  'adupload.accepted_status' => 'awaiting approval'
));
$query->sortby('created', 'DESC');

$orders = $modx->getCollection('simpleCartOrder',$query);

$output .= '<p>Total bought campaigns: '. count($orders) . '</p>';

// Outputs the product
foreach($orders as $order) {
  $fields = $order->toArray();
  $output .= $modx->getChunk('adminAdApprovalTableRow', $fields);
}

Is xpdo simply unable to do a innerjoin without having an alias defined in the schema? I dont have control over the two simplecart tables (technically I do, but I dont wanna mess them up) but I can define a alias relationship between the adupload table and the simplecart tables, and then make the query to the adupload table instead. Would that solve it?

MODX Staff
10,725 Posts

opengeek Reply #5, 14 years, 4 months ago

You can create any SQL you want with xPDO. But retrieving objects using that SQL requires columns of the defined tables to be returned, mapped to the object fields. This is what an O/RM does.

If you want to work with raw result sets, do reporting queries, etc., you can simply use the PDOStatement created by xPDOQuery->prepare() and execute it manually.

You don't always need to work with objects as results.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

33 Posts

cruiser Reply #6, 14 years, 4 months ago

True, I could always go back and do it "old fashioned", but I'd like to do it the "right" way with Modx and take advantage of the Xpdo layer now that so much effort has gone into it wink

Anyhows, I managed to make it work.

// Get list of ads from database
$query = $modx->newQuery('Adupload');
$query->innerJoin('simpleCartOrderProduct','Product');
$query->innerJoin('simpleCartOrder', 'Order');
$query->select(array('Adupload.*, Product.*, Order.created'));
$query->where(array(
  'Adupload.accepted_status' => 'awaiting approval'
));

$orders = $modx->getCollection('Adupload',$query);
$output = $modx->getChunk('adminAdApprovalTableStart');

$output .= '<p>Total bought campaigns: '. count($orders) . '</p>';

I rewrote the schema file to include aggregates of the simplecart tables and now it's able to do the innerjoins like I want and returns the results to me.

For some reason though, if I want it to select "Order.*" the query returns nothing. Haven't quite figured out what goes wrong there, but I should be able to make do with just that one column.