How to count specific table column using laravel

I have here a laravel query:

DB::table('notifications')
    ->leftJoin('domains', 'domains.domain', '=', 'notifications.data->via_domain')
    ->select('notifications.data->via_domain as domain_name')
    ->groupBy('notifications.data->via_domain')
    ->get();

This shows domain names that exists between the tables ‘notifications’ and ‘domains’. However, I want to count notifications.notifiable_id field in ‘notifications’ by:
DB::table('notifications')
    ->leftJoin('domains', 'domains.domain', '=', 'notifications.data->via_domain')
    ->selectRaw('count(notifications.notifiable_id), notifications.data->via_domain')
    ->get();

However it shows error in SQL syntax.

(You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near

via_domain from notifications left join domains on
domains.domain = not at line 1 (SQL: select
count(notifications.notifiable_id), notifications.data->via_domain
from notifications left join domains on domains.domain =
notifications.data->'$."via_domain"')')

Thanks for the help, minna-san.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I already got it!

Here’s my code:

DB::table('notifications')->leftJoin('domains', 'domains.domain', '=', 'notifications.data->via_domain')
                    ->select('notifications.data->via_domain as domainName', DB::raw('COUNT(notifications.notifiable_id) AS leadsCount'))
                    ->groupBy('notifications.data->via_domain')
                    ->get();

This results to an array showing the domain name and its leadsCount.

Thanks everyone.

Method 2

for example, assume we have data as like below

How to count specific table column using laravel

Couponview::whereBetween('created_at', [$start_date, $end_date])
                ->select('store_id', DB::raw('COUNT(store_id) as store_count'))
                ->groupBy('store_id')
                ->orderBy(DB::raw('COUNT(store_id)'), 'DESC')
                ->take(8)
                ->get();

then We got OutPut Like below example image
How to count specific table column using laravel


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x