How to count specific table column using laravel

Question

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.

is data->via_domain actually within the table or is this a variable? Can you post the two tables you're joining — Option
– Option, Commented Oct 24, 2017 at 6:56
DOMAINS TABLE id, int(10) unsigned, NO, PRI, , auto_increment domain, varchar(255), NO, UNI, , description, varchar(255), YES, , , country_id, varchar(255), YES, , , settings, json, NO, , , captor_id, int(11), YES, , , account_id, int(11), NO, , , owner_email, varchar(255), YES, , , owner_phone, varchar(255), YES, , , created_at, timestamp, YES, , , updated_at, timestamp, YES, , , — Caren Divinagracia
– Caren Divinagracia, Commented Oct 24, 2017 at 7:02
'updated_at', 'timestamp', 'YES', '', NULL, '' 'type', 'varchar(255)', 'NO', '', NULL, '' 'read_at', 'timestamp', 'YES', '', NULL, '' 'notifiable_type', 'varchar(255)', 'NO', '', NULL, '' 'notifiable_id', 'int(10) unsigned', 'NO', 'MUL', NULL, '' 'id', 'char(36)', 'NO', 'PRI', NULL, '' 'data', 'text', 'NO', '', NULL, '' 'created_at', 'timestamp', 'YES', '', NULL, '' — Caren Divinagracia
– Caren Divinagracia, Commented Oct 24, 2017 at 7:03

Caren Divinagracia · Accepted Answer · 2017-10-24 07:20:41Z

0

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.

answered Oct 24, 2017 at 7:20

Caren Divinagracia

31 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harsh Patel · Accepted Answer · 2021-02-25 13:01:45Z

0

for example, assume we have data as like below

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

answered Feb 25, 2021 at 13:01

Harsh Patel

1,43414 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to count specific table column using laravel

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related