PHP Yii2 ActiveRecord using nested where condition

Question

I want to create an activeQuery using model::find()->where... that should generate sql like one below ( nevermind the logic ). No joins, just a lot of where conditions for single table. I guess it should be simple and I'm just missing somewhere.
I'm not able to figure out how to create such nested where conditions. Any hints?

select * from article where 
( 
  (site="domain.com" AND service="electronics" AND source="reader" AND tag like "%" AND marker like "%" ) 
  OR 
  (site="domain.net" AND service="electronics" AND source like "%" AND tag like "%" AND marker like "%" )
) 
AND NOT 
( 
  (site="domain.com" AND service="electronics" AND source like "%" AND tag like "%" AND marker like "%" ) 
  OR
  (site="domain.us" AND service="electronics" AND source like "%" AND tag like "%" AND marker like "%" ) 
)

Nebulosar · Accepted Answer · 2018-08-03 11:10:33Z

2

You could try nesting it by using the 'and'and 'or' operators:

['and', 'type=1', 
    ['or', 'id=1', 'id=2']
]

will generate
type=1 AND (id=1 OR id=2)

More info: https://www.yiiframework.com/doc/api/2.0/yii-db-queryinterface#where()-detail

answered Aug 3, 2018 at 11:10

Nebulosar

1,8873 gold badges23 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2015-08-31 11:11:32Z

0

The simplest way could be using findBySql

this way

$myQuery = 'select * from article where 
( 
  (site="domain.com" AND service="electronics" AND source="reader" AND tag  like "%" AND marker like "%" ) 
  OR 
  (site="domain.net" AND service="electronics" AND source like "%" AND tag  like "%" AND marker like "%" )
) 
AND NOT 
( 
  (site="domain.com" AND service="electronics" AND source like "%" AND tag like "%" AND marker like "%" ) 
  OR
  (site="domain.us" AND service="electronics" AND source like "%" AND tag like "%" AND marker like "%" ) 
)';

$model = Article::findBySql($myQuery)->all();

Otherwise if you want Active Data Provider you can do this way

$dataProvider = new ActiveDataProvider([
   'query' => Article::findBySql($myQuery)->all(),
   'pagination' => [
       'pageSize' => 10,
   ],
   'sort' => [
     ........
    ],
]);

edited Aug 31, 2015 at 11:11

answered Aug 31, 2015 at 10:03

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

8 Comments

Kubber Over a year ago

But if I do so I cannot use dataprovider and gridview. GridView won't be searchable/sortable when using findBySql . I'm using findBySql at the moment, but it would help me a lot if I could use AR.

ScaisEdge Over a year ago

Using myQuery as value for query you can do what you need (dataProvider). I have update the answer

Kubber Over a year ago

But it won't work because for gridview I need SearchModel, and when using findBySql() "note that because the SQL statement is already specified, calling additional query modification methods (such as where(), order()) on the created " - ote that because the SQL statement is already specified, calling additional query modification methods (such as where(), order()) on the created .. "

ScaisEdge Over a year ago

I don't understand explain better. In DataProvider you set the query you prefer.the same you can do in search.(is Another dataprovider) In every situation you can use this kind of query is not o problem where you use it. If the query you show is what you need you can use it with findBySql in all method you need

Kubber Over a year ago

but in GridView i need to use filterModel to be able to search and unless i start writing a lot of SQL I guess it won't work as it has to add ->andFilterWhere (if you take a look at any example generated by gii ) . and ->andFilterWhere won't work with findBySql. My question is how to create such query in AR or to know if this impossible, not to use findBySql as this is a bit different story .

|

Kubber · Accepted Answer · 2015-08-31 16:12:22Z

0

Ok I've found the answer. I used Model::find()->where() and built nested arrays. This way I can use ActiveRecord and all gridview/dataprovider features.

answered Aug 31, 2015 at 16:12

Kubber

3974 silver badges13 bronze badges

1 Comment

Stijn de Witt Over a year ago

I hate it when people accept their own answers without actually giving any details on the solution. At least add a code sample of the code you ended up with. Saying 'I found the solution` does not help anyone else.

Collectives™ on Stack Overflow

PHP Yii2 ActiveRecord using nested where condition

3 Answers 3

Comments

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related