sql select case and 2 joins - representation in propel

Showing 1-7 of 7 messages
sql select case and 2 joins - representation in propel wojciech777 9/15/11 11:47 PM
Can anyone can help me in presenting the sql in propel, please?

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...
Re: [propel] sql select case and 2 joins - representation in propel Murray Collingwood 9/16/11 12:43 AM
Hi [whoever]

Please note - this is a group where you are supposed to identify yourself with a name.



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

The Criteria() class model is so yesterday.

Have a look at this page and start with:

$result = AQuery::create()
  ->withColumn("
case when A.Creator_user_id IS NULL THEN B.First_name||' '||B.Last_name else C.First_name||' '||C.Last_name end","creator_first_last_name")
   ->
select(array('A.Commission_id', 'creator_first_last_name'))

   ->join('B')
   ->join('C')
   ->find();

Cheers
mc





--
Murray Collingwood
Focus Computing
p +61 415 24 26 24
http://www.focus-computing.com.au
Re: [propel] sql select case and 2 joins - representation in propel Murray Collingwood 9/16/11 12:44 AM
Woops - forget the URL: http://www.propelorm.org/reference/model-criteria.html

Cheers
mc
Re: sql select case and 2 joins - representation in propel wojciech777 9/16/11 1:11 AM
Hi Murray.

Yes, but this should be additional criteria in my executList action in
actions.class.php (symfony) also with pager:

                        // pager
                        $this->pager = new sfPropelPager('commission', 20);
                        $c = new Criteria();

select case with joins goes here

                        $this->dbInit($c);
                        $this->addSortCriteria($c);
                        $this->addFiltersCriteria($c);
                        $this->pager->setCriteria($c);

Cheers
Wojciech Kaźmierczak
Re: sql select case and 2 joins - representation in propel Panos Kyriakakis 9/17/11 2:34 PM
Hi,

picking the code from Murray's post
$result = AQuery::create()
  ->withColumn("case when A.Creator_user_id IS NULL THEN
B.First_name||'
'||B.Last_name else C.First_name||' '||C.Last_name end","
creator_first_last_name")
   ->select(array('A.Commission_id', 'creator_first_last_name'))
   ->join('B')
   ->join('C')
   ->find();

you can

$q = AQuery::create()
  ->withColumn("case when A.Creator_user_id IS NULL THEN
B.First_name||'
'||B.Last_name else C.First_name||' '||C.Last_name end","
creator_first_last_name")
   ->select(array('A.Commission_id', 'creator_first_last_name'))
   ->join('B')
   ->join('C');

Now $q is modelCriteria object and you can pass it to your other
methods like you did with Criteria and use ModelCriteria pager (nice
and fast).
Those days I am converting code to replace Criteria with
ModelCriteria.

Best regards
Re: sql select case and 2 joins - representation in propel wojciech777 9/19/11 3:52 AM
Thanks Panos.

Anyway, "we" use Symfony 1.0 with no option for upgrade. So I'm
affraid modelCriteria will be available only via DbFinder.

In addition I ended with:

                $c->addAsColumn("creator_first_last_name",
                        "CASE WHEN COMMISSION.CREATOR_USER_ID IS NULL THEN
                            (SELECT ACCOUNT_USER.FIRST_NAME||' '||ACCOUNT_USER.LAST_NAME
FROM ACCOUNT_USER WHERE USER_ID = COMMISSION.CREATOR_ACCOUNT_USER_ID)
                            ELSE
                            (SELECT USER_USER.FIRST_NAME||' '||USER_USER.LAST_NAME FROM
USER_USER WHERE USERID = COMMISSION.CREATOR_USER_ID)
                            END");
                $criterion1 = $c->getNewCriterion($c-
>getColumnForAs('commission.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
COMMISSION]
Re: sql select case and 2 joins - representation in propel xplo 9/20/11 2:43 PM
Hi,

sorry if it s not the answer you re looking for but i would move this
logic from the sql to the activerecord model so it would :
.be easy to code/maintain (that s the point of an ORM)
.be slower (more sqls at least with propel < 1.5)