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

Critchfield

Can you explain why you don't have relationships declared between two tables that are joinable? – Shuttle

(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). – Shuttle

@halfer: Thx for the formatting! – Critchfield

@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, ... – Critchfield

Answer 1

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.

Orangy

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... – Critchfield

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 – Orangy

Answer 2

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)

Fostoria

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 – Odilia

On Propel 1.4.x you need to use: Criteria::INNER_JOIN instead of , Criteria::INNERJOIN. – Convector

On Propel 2 the syntax is slightly different: \TableAQuery::create()->addJoin(\TableATableMap::COL_THIS_COLUMN, \TableBTableMap::COL_THAT_COLUMN, Criteria::INNER_JOIN); – Uriisa

As the author of the currently accepted answer, I ask @Thomas to accept this answer, because it's the better solution. Thanks! – Orangy

Correct Answer ! – Nucleonics

Answer 3

Here's how you can do it via addSelectQuery in Propel 2 which basically creates a CROSS JOIN. This is joining the Item entity with Product entity, example is from Spryker:

     return $this->getFactory()
        ->createPyzResourceToOrderQuery()
        ->select([
            PyzResourceToOrderTableMap::COL_FK_CUSTOMER,
            PyzResourceToOrderTableMap::COL_FK_SALES_ORDER,
        ])
        ->leftJoin('SpySalesOrder.Item')
        ->addSelectQuery(
            $this->getFactory()->createProductQuery()
                ->addAsColumn('fk_product_abstract', SpyProductTableMap::COL_FK_PRODUCT_ABSTRACT)
                ->addAsColumn('sku', SpyProductTableMap::COL_SKU),
            't',
        )
        ->where('t.sku = Item.sku')...

Sthenic