Counting rows for the column in mysql

Question

My problem is simple. I have two tables

transaction_bodies ------------------ body_id full_name

and the other one is

transaction_accounts -------------------- account_id body_id account_name

Relation is one to many. One body can have multiple accounts. I am trying to create a query that counts the accounts that bodies have.

I tried this

 SELECT * FROM ( SELECT count(*) as trans, tb.full_name FROM transaction_accounts ta LEFT JOIN transaction_bodies tb ON tb.body_id = ta.body_id ) as row;

But this doesn't give the right result. Can anyone help me out with this? And if can provide how to write sub-queries in Laravel that would be a appreciated much.

You have tagged this with Laravel, are you using Eloquent (Laravel models)? — James Fenwick
– James Fenwick, Commented Jun 18, 2017 at 9:12

Nazmul Hasan · Accepted Answer · 2017-06-18 09:24:32Z

Try this :

$result = DB::table('transaction_bodies') ->leftJoin('transaction_accounts as ta','transaction_bodies.body_id','ta.body_id') ->select(DB::raw('count(ta.account_id) AS trans'),'transaction_bodies.full_name') ->groupBy('transaction_bodies.body_id') ->get();

Darshan Mehta · Accepted Answer · 2017-06-18 08:55:28Z

You can do it with LEFT JOIN, e.g.:

SELECT tb.body_id, COUNT(ta.*) FROM transaction_bodies LEFT JOIN transaction_accounts ta ON tb.body_id = ta.body_id GROUP BY tb.body_id;

Ali Rasheed · Accepted Answer · 2017-06-18 10:19:41Z

With a simple LEFT JOIN you can achieve it like

SELECT tb.full_name, COUNT(account_id) as accounts FROM transaction_bodies tb LEFT JOIN transaction_accounts ta ON tb.body_id = ta.body_id GROUP BY tb.body_id;

In Laravel you can do it like with model

$accounts = Transaction_body::leftJoin('transaction_accounts as ta','transaction_bodies.body_id','ta.body_id')->groupBy('transaction_bodies.body_id')->get();

without model

$accounts = DB::table('transaction_bodies')->leftJoin('transaction_accounts as ta','transaction_bodies.body_id','ta.body_id')->groupBy('transaction_bodies.body_id')->get();

Ahmad Tarawneh · Accepted Answer · 2017-06-18 12:50:46Z

 /** * Class Body */ class Body extends Model { /** * The database table used by the model. * * @var string */ protected $table = 'transaction_bodies'; /** * Get the accounts for the Transaction Body. */ public function accounts() { return $this->hasMany(Account::class); } } /** * Class Account */ class Account extends Model { /** * The database table used by the model. * * @var string */ protected $table = 'transaction_accounts'; /** * Get the body that owns the account. */ public function body() { return $this->belongsTo(Body::class); } } //usage $accounts = Body::find(1)->accounts;

https://laravel.com/docs/5.4/eloquent-relationships#one-to-many

Collectives™ on Stack Overflow

Counting rows for the column in mysql

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related