How can I have "orWhereNotNull" on a collection?

@Nakov thank you!

@Tray2 I might be wrong but I need 2 pieces of information - the rows returned by the main query, and then from the collection I get, get specific columns with certain conditions. So right now I have:

$main_data = MyModel->..some query ..->get();

Because I need to display the entire data above, and then from this result set I also need to count how many columns meet specific conditions (the WhereNotNull and orWhereNotNull)

@Ligonsker Sound to me like you need a union that handles that for you in the dabase, or possibly a suq-query or two.

@Tray2 Since I am not really proficient in SQL, I usually make separate queries instead of subqueries. Do you think it can affect performance? Right now I ended up doing what you said: Instead of doing that with the PHP side, I created another query:

$query = MyModel->..some query;
$sub_data = with clone($query)->whereNotNull('some_column')->orWhereNotNull('some_other_column')->get();
$main_data = $query->get();

It works, but instead of a single query with sub-queries, I split to 2 queries. How badly does it affect performance? Or, a sub query is 2 queries behind the scenes as well?

@Ligonsker It depends, there is nothing really wrong with running two queries like that, it would probably be faster with a single query, but not necessarilly, it all depends on the data. If you have debugbar installed, it will tell you how much time it took. you can't really plan for how fast a query needs to be, but I know you will see that the two queries is faster than filtering it with php, or at least use less resources.

@Tray2 This is nice to know, because I was always sure that PHP would do it faster, because in my case the DB sits on a different server than the PHP server so I thought that just the connection time will be worse. (In my head I see every query, even a simple one, as a long connection from the PHP server to the DB server and that's why I was preferring the PHP filtering for data set that isn't too large)

@Ligonsker to understand why you cannot chain orWhere() etc, you have to realise that ->where() is acting like a pipeline. The output of the where() is all the items matching the where statement. You cannot then fluently chain on or anything because you have already stripped out the non-matching items.

Neither can you use get() on a collection

you can use a filter like @nakov says, or add the counts;

$count = 
		$collection->whereNotNull('some_col')->count() + 
		$collection->whereNotNull('some_other_col')->count();