querybuilder pass column to nested query

I would like to query the db to retrieve all events that do not have a comment created after the start date. Following structure for EVENT and COMMENT

EVENT 
id 
start 
created_at 
updated_at

COMMENT
id
comment
event_id
created_at
updated_at

Create a query ( $query = Event::query()->with('comments'); ) which gives me the following construct.

[{
    "id": 1,
    "start": "2018-06-18 12:00:00",
    "created_at": "2018-06-18 11:50:07",
    "updated_at": "2018-07-02 12:13:15",
    "comments": [
        {
            "id": 1,
            "created_at": "2018-06-18 12:44:35",
            "updated_at": "2018-06-18 12:44:35",
            "comment": "cascsac"
        }
    ]
}]

I want to retrieve all entries where comment.created_at is after the start date of the event. I tried with the following:

   $query->orWhere(function ($query) {
      $value = $query->where(function ($query) use ('start') {
        $query->whereHas('comments', function ($query) use ('start') {
          $query->where('created_at','<=','start');
          //->whereColumn('created_at', '<=', 'start');
        });
      });
    });

But its does not like my attempts to pass start to a nested query and am not sure how to pass it for whereColumn query.

How do I pass values from columns to a nested where. Or how else do I query a with the value of a separate column or how do I convert a value of a column to a variable I can pass to a nested query?

Cinek

7 years ago

Best Answer

Level 6

$events = Event::with('comments')->whereHas('comments',function ($query) {
            $query->whereColumn('comments.created_at','<','events.start');
        })->get();

whereColumn is function to compare columns in query.

1 like

Please or to participate in this conversation.