Using MySQL functions in Propel ORM

Question

I need help in writing the following raw MYSQL query in Propel ORM in "symfony 1.1.9" framework which contains MySQL functions in SELECT and WHERE.

SELECT DATE( u.date ) date, TIME( u.date ) time, u.id account_id, u.user_name ,
        IF( ( SELECT COUNT( id )
        FROM bank
        WHERE user_id = u.id
        AND DATE( creation_date ) > DATE_ADD( CURDATE( ) , INTERVAL -30
        DAY )
        GROUP BY user_id ) , 'Y', 'N'
        ) AS deposited
        FROM user u
        WHERE DATE( u.dat ) > DATE_ADD( CURDATE( ) , INTERVAL -30
        DAY )

I searched a lot for this. but could't get any relevant solution. I tried this with many possible solution. But nothing works. Note that i'm not prefer to use raw query . Please help. Any help would be greatly appreciated. Thanks in advance.

muz the axe muz the axe · Accepted Answer · 2014-03-13T13:05:26

The best article I have found is the one on this page: http://propelorm.org/reference/model-criteria.html#using-a-query-as-input-for-a-second-query-table-su Refer to the section of Subqueries.

To get you started you might try something like the following (I haven't tested this so it's bound to have some bugs):

$deposited = BankQuery::create()
->filterByCreationDate($back30days, Criteria::GREATER)
->groupBy('Bank.UserId');

$users = UserQuery::create()
->addSelectQuery($deposited, 'deposited')
->where('deposited.UserId = User.Id')
->filterByDate($back30days, Criteria::GREATER)
->find();

Using MySQL functions in Propel ORM

1 Answers