select entries based on date (expiration_date)

@Nakov Is not working like I want.

It returns 1 entry (1 contract with 4 documents (where only 2 of them has expirarion_date set))

Using my aproach with query builder I am getting 5 entries (but in DB are 4 contracts) one is more because contract with id for example 658 has 2 documents with expiration_date set (not nullable).

To get the right entries .... it should be 4. I need somehow to get only one (the latest) expiration_date if it's set for the document. How can I do that ? I hope I make my self clear....

@calin.ionut I don't think I follow. My code above will return ALL contracts with their documents that are finishing within April.

@Nakov the query builder that I wrote it returns this:

contract_id is_contract expiration_date
658	  1  	2022-04-02
658	  0    2022-04-02
829	  1 	2022-04-14
830	  1 	2022-04-29
832	  1 	2022-04-30

As you can see the contract with id 658 is twice (it should be once). The result is ok (there are only 4 contracts that expires in april)

@calin.ionut try this:

DB::table('contracts')
  ->leftJoin('documents', 'contracts.id', '=', 'documents.contract_id')
  ->whereNotNull('expiration_date')
  ->whereMonth('expiration_date', '04')
  ->whereYear('expiration_date', '2022')
  ->select('documents.contract_id', 'documents.is_contract', DB::raw('max(documents.expiration_date) as expiration_date'))
  ->groupBy('documents.contract_id', 'documents.expiration_date')
  ->havingRaw('max(expiration_date)')
  ->get();

@Nakov I am getting that error:

Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GroupBy clause and contains nonaggregated column documents.is_contract ....

@calin.ionut but if you add is_contract in your group then again you will be getting two records for the same contract id.. don't you need just those that are really a contract? Because for 658 you've got 1 and 0, so if you want just 1 then this one below will work. If you want both, then you can add that column to the group by which will give you duplicates yet again.

DB::table('contracts')
  ->leftJoin('documents', 'contracts.id', '=', 'documents.contract_id')
  ->whereNotNull('expiration_date')
  ->whereMonth('expiration_date', '04')
  ->whereYear('expiration_date', '2022')
  ->select('documents.contract_id', DB::raw('max(documents.expiration_date) as expiration_date'))
  ->where('documents.is_contract', true)
  ->groupBy('documents.contract_id', 'documents.expiration_date')
  ->havingRaw('max(expiration_date)')
  ->get();

@Nakov I need both.

is_contract tells me if is a contract or appendix.

If I remove from select documents.is_contract or other fields that I need in select it will show me the 4 entries.

How can I fix this ?

@calin.ionut I don't know.. it will show you duplicate like this yet again.

DB::table('contracts')
  ->leftJoin('documents', 'contracts.id', '=', 'documents.contract_id')
  ->whereNotNull('expiration_date')
  ->whereMonth('expiration_date', '04')
  ->whereYear('expiration_date', '2022')
  ->select('documents.contract_id', 'documents.is_contract', DB::raw('max(documents.expiration_date) as expiration_date'))
  ->groupBy('documents.contract_id', 'documents.expiration_date', 'documents.is_contract')
  ->havingRaw('max(expiration_date)')
  ->get();

another issue .... I am getting four entries..... but If put count() instead of get() it return two not four.

Any idea why ???

@calin.ionut so unreliable :) it can return either 1 or 0, and you'll never know which one was last, but you'll think you had the correct result.

I would switch the column expiration_date from a type date to a timestamp that way you will always get the correct result.

DB::table('contracts')
  ->leftJoin('documents', 'contracts.id', '=', 'documents.contract_id')
  ->whereNotNull('expiration_date')
  ->whereMonth('expiration_date', '04')
  ->whereYear('expiration_date', '2022')
  ->select(DB::raw('distinct documents.contract_id'), 'documents.is_contract', DB::raw('max(documents.expiration_date) as expiration_date'))
  ->groupBy('documents.contract_id', 'documents.expiration_date', 'documents.is_contract')
  ->havingRaw('max(expiration_date)')
  ->get();

@Nakov another solution could be adding a flag in documents table and set true for the last document that was added expiration_date. Then I know if I have expiration_date set and is the last one :)

@calin.ionut and you'll have to switch any of the previous once as false which does not make sense.

It is up to you :)