CODEWITHSUNDEEP
phpmysqllaraveleloquent
Howard
Howard

Reputation: 3758

How can I combine this query?

I'm using Laravel Eloquent to hit the database twice. I merge the results of the 2 queries into one. How can I make this into a single query so it only hits the database once?

$openers = Comments::select('id', 'parent_id', 'opener_id', 'topic', 'username', 'comment', 'upvotes', 'created_at', 'updated_at', 'deleted_at')
    ->where('topic', '=', $topic)
    ->where('parent_id', '=', 0)
    ->orderBy('id')
    ->orderBy('updated_at', 'desc')
    ->get();

$replies = Comments::select('id', 'parent_id', 'opener_id', 'topic', 'username', 'comment', 'upvotes', 'created_at', 'updated_at', 'deleted_at')
    ->where('topic', '=', $topic)
    ->where('parent_id', '!=', 0)
    ->orderBy('parent_id')
    ->orderBy('updated_at', 'desc')
    ->get();

$comments = $openers->merge($replies);

Basically I'd like the rows with parent_id: 0 to be sorted by id and the rows where the parent_id is not 0 to be sorted by parent_id but NOT by id.

Upvotes: 2

Views: 63

Answers (2)

Kenneth Sunday
Kenneth Sunday

Reputation: 895

I know your figuring out something else, cause in basic query you can actually obtain your issue already.

Sample query

or if you want to do some complex query but your having issue on how to do it via ORM method. You can create a trick the Model by creating a "VIEW" table in your database and then do the complex query in that VIEW with the same columns on your table or adding/remove column too! :)

you can do something like this,

in your SQL database

CREATE VIEW `sample_database`.`view_comment` AS 
SELECT *, (SELECT some_field FROM other_table) as extra_field FROM comment
WHERE (some_field = 'SOME_CONDITION_ONLY') AND
some_date BETWEEN 'FROM_DATE' AND 'END_DATE'

then in your Php laravel code, you can create a 'ViewComment' model, then you can do a query something like this still.

ViewComment::where('some_field','YOUR_CONDITION')->get(); //or
ViewComment::paginate(10);

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Remove condition (parent_id = 0 ,parent_id != 0 ) and add conditional order by to sort all comments first and then all replies, Another conditional order by to sort comments by id and replies by parent_id

$results = Comments::select(
    'id', 'parent_id', 'opener_id', 'topic', 'username', 'comment', 'upvotes', 'created_at', 'updated_at', 'deleted_at',
    DB::raw("CASE WHEN parent_id = 0 THEN 'Comments' ELSE 'Replies' END as comment_type")
)
    ->where('topic', '=', $topic)
    ->orderByRaw('parent_id = 0 DESC')
    ->orderByRaw('CASE WHEN parent_id = 0 THEN id ELSE parent_id END ASC')
    ->orderBy('updated_at', 'desc')
    ->get();

Upvotes: 1

Related Questions