Propel ORM - Joining unrelated tables

Question

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!)

Can you explain why you don't have relationships declared between two tables that are joinable? — halfer, Sep 19 '13 at 08:46
(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, Sep 19 '13 at 08:48
@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, Sep 19 '13 at 09:07

score 42 · Answer 1 · edited Oct 13 '16 at 16:10

42

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)

edited Oct 13 '16 at 16:10

izb

50,101
39
117
168

answered Jun 22 '14 at 23:05

Qiniso

2,587
1
24
30

You should have emphasised that `TableAPeer::ThisCOLUMN` and `TableBPeer::ThatCOLUMN` must be in the order shown in `addJoin()` - if you don't it won't work – Daniel Jan 08 '16 at 01:17
On Propel 1.4.x you need to use: Criteria::INNER_JOIN instead of , Criteria::INNERJOIN. – Jestep Mar 31 '16 at 15:46
3

On Propel 2 the syntax is slightly different: `\TableAQuery::create()->addJoin(\TableATableMap::COL_THIS_COLUMN, \TableBTableMap::COL_THAT_COLUMN, Criteria::INNER_JOIN); ` – user654123 Nov 16 '16 at 17:04
As the author of the currently accepted answer, I ask [@Thomas](https://stackoverflow.com/users/2794595/thomas) to accept this answer, because it's the better solution. Thanks! – twigmac Sep 29 '17 at 13:36
Correct Answer ! – Madushan Perera Dec 01 '18 at 03:56

twigmac · Accepted Answer · 2013-10-05T08:35:27.790

0

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.

edited Oct 05 '13 at 08:35

answered Sep 23 '13 at 09:43

twigmac

1,772
3
21
38

That would indeed work for my posted yet simplified example. However, in the application I'm working with the query consists of so many related tables (extensive use of ->use()) that this would be next to impossible to implement :-( I'll probably work around it using several (separate) queries and then work with objects to "manufacture" the result format I need... – Thomas Oct 03 '13 at 17:13
Okay, I see. Anyway, thanks for accepting the answer :). Your idea of "manufacturing" the result from different objects seems to be the way to go then! Good luck – twigmac Oct 05 '13 at 08:34

Propel ORM - Joining unrelated tables

2 Answers2

Linked