CODEWITHSUNDEEP
phpmysqlormpropel
mr smok
mr smok

Reputation: 11

Propel "AND NOT" query

I'm using the propel ORM in my PHP application.

From reading the docs, I can't figure how to make a request of this type:

SELECT x FROM table where col1 = 'xxx' and not(col2=1 AND col3=2);

What is the cleanest way to perform this request using pure propel logic?

Thanks

Upvotes: 0

Views: 925

Answers (2)

GiuServ
GiuServ

Reputation: 1235

The complete query you're looking for should be this one:

$books = TableQuery::create()
    ->condition('cond1', 'col1 = ?', 'xxx')
    ->condition('cond2', 'col2 != ?', 1)
    ->condition('cond3', 'col3 != ?', 2)
    ->combine(array('cond2', 'cond3'), 'or', 'cond23')
    ->where(array('cond1', 'cond23'), 'and')
    ->find();

It creates:

  • a cond1 condition where col1 = 'xxx'
  • a cond2 condition where col2 != 1
  • a cond3 condition where col3 != 2

Then it combines cond2 and cond3 with a or operator in a new cond23, so that 

cond23 = cond2 or cond3

and combines cond23 and cond1 with a and operator, so that the final query will be

where(cond1 and cond23)

Upvotes: 3

chocochaos
chocochaos

Reputation: 1596

Your query would be equivalent to:

SELECT x FROM table where col1 = 'xxx' and (col2 != 1 OR col3 != 2);

Assuming you are using propel 2, you should be able to accomplish what you'd like with:

$result = TableQuery::create()
    ->filterByCol1('xxx')
    ->filterByCol2(1, Criteria:NOT_EQUAL)
    ->_or()
    ->filterByCol3(2, Criteria:NOT_EQUAL)
    ->find();

Upvotes: 1

Related Questions