Propel ORM - Joining unrelated tables

9
votes

How does this SQL statement translate into Propel (1.6.3)?

SELECT * FROM table_a JOIN table_b

With tableA and tableB sharing no Foreign Keys and having no relationships defined.

TableAQuery::create()->join('tableB')

doesn't work since Propel complains with an error:

"Fatal error: Uncaught exception 'PropelException' with message 'Unknown relation TableB on the TableA table'

Thanks very much in advance for any help and hints! This is my first post here and I hope I haven't done anything wrong yet :-) (I've searched thoroughly before I posted!)

2
phpsqlormpropel
Can you explain why you don't have relationships declared between two tables that are joinable? – halfer
(Your question seems okay to me - prior research and attempt. I've added a bit of code and quote formatting - you can use the buttons above the question box to achieve this). – halfer
@halfer: Thx for the formatting! – Thomas
@halfer: The relationship between the two tables is not making sense within the business logic of the application but needed for an automated export for further processing. It's like joining tables "houses h" with "fruits f" to get all possible combinations: h1-f1, h1-f2, h1-f3, h2-f1, h2-f2, h2-f3, ... – Thomas

2 Answers

1
votes

You can work around this limitation by using raw SQL syntax. For instance:

$con = Propel::getConnection(SomePeer::DATABASE_NAME);
$query = 'SELECT * FROM `table_a` JOIN `table_b` LIMIT 10';
$stmt = $con->prepare($query);
if($stmt->execute()) {
    $res = $stmt->fetchAll();
    var_dump($res);
}

Note #1: These kind of joins can become very big and quickly exhaust the allowed memory size. That's why I've added a LIMIT.

Note #2: The output isn't very clean, arrays of both numeric and associative keys. Maybe there are ways to improve this.

40
votes

You could also use "addJoin" like this:

TableAQuery::create()
->addJoin(TableAPeer::ThisCOLUMN, TableBPeer::ThatCOLUMN, Criteria::INNER_JOIN); //Can also be left/right

The third argument also takes left and right join.

And, instead of the usual "filterByXXX()"

->filterByOtherColumn(value)

you'd use "add()", like this:

->add(TableAPeer::OtherCOLUMN, value)