newquery with more than one table

- 67 Posts
jimbob72 Reply #1, 14 years, 7 months ago
Is it possible to prepare a newquery on more than one table (tables that have no specific relationship but similar column titles)?

e.g. Returning items from table1 AND table2 that have an id of 3.
```
$a = $modx->newQuery({'table1','table2'});
$a->where(array('id' => 3,));
$results = $modx->getCollection({'table1','table2'},$a);
etc...
```

67 Posts

jimbob72 Reply #2, 14 years, 7 months ago

Couldn't find an xPDO solution, but worked out a PDO solution instead:

$stmt = $modx->query('
SELECT *
FROM table1 t1 
WHERE t1.id = 3
UNION ALL
SELECT *
FROM table2 t2
WHERE t2.id = 3
');

if ($stmt) { 
      while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $output .= $modx->getChunk($tpl,$row);
   }
return $output;
}