CODEWITHSUNDEEP
sqllaravel
Akki Verma
Akki Verma

Reputation: 49

compare and count from two tables using laravel

id  sm_id  to_id
1   3      2
2   4      1
3   3      3
4   3      2
5   3      3
6   4      1

How can I count how many occurences of a to_id an sm_id has?

I have two inputs, first sm_id and second to_id, I want to count occurence of values based on inputs.

Expected result for sm_id = 4 and to_id = 1 would be 2

Here's the code I tried

$us = \DB::table('mgs')
                 ->where('sm_id', '=', DB::raw('to_id'))
        ->where('to_id', '=', DB::raw('sm_id'))
                 ->groupBy('sm_id')
                 ->count();

      print_r($us);die;

Upvotes: 1

Views: 571

Answers (3)

Abhay Maurya
Abhay Maurya

Reputation: 12277

If you have $sm_id and $to_id as an input then you dont need to use DB::raw as you are using Laravel query builder so it will take care of SQL injection:

$us = \DB::table('mgs')->where(['sm_id'=>$sm_id,'to_id'=>$to_id])->count();
dd($us);

dd() is method of laravel which stands for dump and die so you dont need to use print_r and die separately.

PS. I have used \DB from your script but I advise to use: Illuminate\Support\Facades\DB instead.

Upvotes: -1

Alexey Mezenin
Alexey Mezenin

Reputation: 163768

If you want to count how many messages with given sm_id and to_id in the table, do this:

\DB::table('msg')->where('sm_id', $smId)->where('to_id', $toId)->count();

Upvotes: 2

Pankit Gami
Pankit Gami

Reputation: 2553

Try this :

$us = \DB::table('mgs')
            ->where('sm_id', '=', DB::raw('to_id'))
            ->where('to_id', '=', DB::raw('sm_id'))
            ->groupBy('sm_id', 'to_id')
            ->count('sm_id', 'to_id');

print_r($us);die;

Upvotes: 0

Related Questions