This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use Stack Overflow.

symfony propel, one new column from 2 joins from 2 columns

Notes and reports about symfony 1.0.x stable

symfony propel, one new column from 2 joins from 2 columns

by wojciech777 » Wed Sep 14, 2011 9:11 am

Hi there.

Can anyone help me to find solution for my problem?

I have 1 table - documents:
document_id, user_id, creator_account_id, creator_user_id

and 2 related tables:
creator_account:
account_id, first_name, last_name

creator_user:
user_id, first_name, last_name

is there any way to get 1 table (for criteria in list action) with data:

documents:
document_id, user_id, creator_first_last_name ?

collecting first and last names depending on the type of relation? (creator_account_id, creator_user_id)?

or what would be the best solution to sort documents by name and surname of the creator (propel only) in this situation?

Kind regards
Wojciech K.
wojciech777
Junior Member
 
Posts: 3
Joined: Wed Sep 14, 2011 9:00 am

Re: symfony propel, one new column from 2 joins from 2 colum

by wojciech777 » Fri Sep 16, 2011 8:43 am

Ok, sql looks like that:

select a.commission_id,
case
when a.creator_user_id IS NULL THEN b.first_name||' '||b.last_name
else c.first_name||' '||c.last_name
end as creator_first_last_name
from commission a
left join account_user b on a.creator_account_user_id = b.user_id
left join user_user c on a.creator_user_id = c.userid

what will be its representation in propel?

$c = new Criteria();
$c->add...
wojciech777
Junior Member
 
Posts: 3
Joined: Wed Sep 14, 2011 9:00 am

Re: symfony propel, one new column from 2 joins from 2 colum

by wojciech777 » Mon Sep 19, 2011 1:05 pm

In addition I ended with:
$c->addAsColumn("creator_first_last_name",
"CASE WHEN DOCUMENTS.CREATOR_USER_ID IS NULL THEN
(SELECT CREATOR_ACCOUNT.FIRST_NAME||' '||CREATOR_ACCOUNT.LAST_NAME
FROM CREATOR_ACCOUNT WHERE ACCOUNT_ID = DOCUMENTS.CREATOR_ACCOUNT_USER_ID)
ELSE
(SELECT CREATOR_USER.FIRST_NAME||' '||CREATOR_USER.LAST_NAME FROM
CREATOR_USER WHERE USER_ID = DOCUMENTS.CREATOR_USER_ID)
END");
$criterion1 = $c->getNewCriterion($c->getColumnForAs('documents.creator_first_last_name'), '%'.$this->filters['creator'].'%', Criteria::ILIKE );
$c->add($criterion1);
and with error:
[PropelException]
[wrapped: Cannot fetch TableMap for undefined table: CASE WHEN
DOCUMENTS]
wojciech777
Junior Member
 
Posts: 3
Joined: Wed Sep 14, 2011 9:00 am