PHP - passing arguments from an array to SQL query

I have an SQL query, where I would like to pass different parameters based on different scenarios. I am not sure how to do achieve this. This is the query:

$data = Engagement::leftJoin('user_engagement', 'engagements.id', '=', 'user_engagement.engagement_id')
            ->select('engagement_id', 'engagements.question', 'engagements.id')
            ->selectRaw('count(*) as count')
            ->whereBetween('user_engagement.created_at', [$from.' 00:00:00', $to.' 00:00:00'])
            ->groupBy('user_engagement.engagement_id')
            ->orderBy('count', 'DESC')
            ->take(5)
            ->get()->toArray();

So, what I would like to do this, would look something like this:

$model = new Engagement;
            $leftJoin = ['user_engagement', 'engagements.id', '=', 'user_engagement.engagement_id'];
            $select = ['engagement_id', 'engagements.question', 'engagements.id'];
            $whereBetween = 'user_engagement.created_at';
            $groupBy = 'user_engagement.engagement_id';

            $data = $model->leftJoin($leftJoin)
            ->select($select)
            ->selectRaw('count(*) as count')
            ->whereBetween($whereBetween, [$from.' 00:00:00', $to.' 00:00:00'])
            ->groupBy($groupBy)
            ->orderBy('count', 'DESC')
            ->take(5)
            ->get()->toArray();

bzbislawski

9 years ago

Level 4

Let's say you want two scenarios:

Return 5 rows
Return 10 rows based on certain conditions.

$data = Engagement::leftJoin('user_engagement', 'engagements.id', '=', 'user_engagement.engagement_id')
            ->select('engagement_id', 'engagements.question', 'engagements.id')
            ->selectRaw('count(*) as count')
            ->whereBetween('user_engagement.created_at', [$from.' 00:00:00', $to.' 00:00:00'])
            ->groupBy('user_engagement.engagement_id')
            ->orderBy('count', 'DESC');

if (condition) {
    $data->take(5);
}
else if(condition) {
    $data->take(10);
}

return $data->get()->toArray();

Leff7

9 years ago

Level 4

But, I need to do that for every statement. It is not the case of the number of rows I want to take, it is the matter of passing variables to eloquent statements, like leftJoin and select.

Please or to participate in this conversation.