Join query exception in Symfony 1.4.11/Propel 1.4.

Question

I need to run following query:

SELECT 
    m.TITLE, 
    m.MOMENTOIMAGE, 
    s.CREATED_AT, 
    s.UNREAD, 
    mem.FIRSTNAME, 
    mem.LASTNAME, 
    mem.MEMBER_PHOTO,
    mem.ID 
FROM `momento_send` s,
    `send_distribution` sd,
    `momento_distribution` d,
    `momento` m,
    `member` mem
WHERE
    s.momento_idmember=6 AND
    sd.id_send=s.id AND
    sd.id_distribution=d.id AND
    d.momento_id=m.id;

For that, I wrote following code in Symfony 1.4 (using Propel 1.4.2) (Thanks to @j0k)

$c = new Criteria();
$c->clearSelectColumns();
$c->addSelectColumn(MomentoPeer::TITLE);
$c->addSelectColumn(MomentoPeer::MOMENTOIMAGE);
$c->addSelectColumn(MomentoSendPeer::CREATED_AT);
$c->addSelectColumn(MomentoSendPeer::UNREAD);
$c->addSelectColumn(MemberPeer::FIRSTNAME);
$c->addSelectColumn(MemberPeer::LASTNAME);
$c->addSelectColumn(MemberPeer::MEMBER_PHOTO);
$c->addSelectColumn(MemberPeer::ID);

$c->addJoin(SendDistributionPeer::ID_SEND, MomentoSendPeer::ID, Criteria::INNER_JOIN);
$c->addJoin(SendDistributionPeer::ID_DISTRIBUTION, MomentoDistributionPeer::ID, Criteria::INNER_JOIN);
$c->addJoin(MomentoDistributionPeer::MOMENTO_ID, MomentoPeer::ID, Criteria::INNER_JOIN);
$c->addJoin(MomentoDistributionPeer::MOMENTO_IDMEMBER, MemberPeer::ID, Criteria::INNER_JOIN);

$c->add(MomentoSendPeer::MOMENTO_IDMEMBER, $memberid);

//echo $c->toString();exit;

$records = SendDistributionPeer::doSelect($c);

Running this code generated following error

[wrapped: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN momento_send ON (send_distribution.ID_SEND=momento_send.ID) INNER JOI' at line 1]

Can someone suggest, what is the issue.

Just to give little more info, SQL printed with echo $c->toString();exit; was

SELECT momento.TITLE, 
    momento.MOMENTOIMAGE, 
    momento_send.CREATED_AT, 
    momento_send.UNREAD, 
    member.FIRSTNAME, 
    member.LASTNAME, 
    member.MEMBER_PHOTO,
    member.ID 
FROM 
    INNER JOIN momento_send ON (send_distribution.ID_SEND=momento_send.ID) 
    INNER JOIN momento_distribution ON (send_distribution.ID_DISTRIBUTION=momento_distribution.ID) 
    INNER JOIN momento ON (momento_distribution.MOMENTO_ID=momento.ID) 
    INNER JOIN member ON (momento_distribution.MOMENTO_IDMEMBER=member.ID) 
WHERE momento_send.MOMENTO_IDMEMBER=6

Answer 1

Try with $c->setPrimaryTableName(MomentoPeer::TABLE_NAME);