count relations

Hi everyone. I have 3 tables described below, I want to find the top provinces which mean those provinces with more transactions according to a special gateway_id. how can I do that? I can not use withCount, because I have 3 tables related to each other. I want to have provinces, I mean first I want to find all provinces in the province table and then count any of them from the transaction and if there is not any province in the transaction_log table, it gives me 0, what should I do? I want to have something like:

Provience::withCount('transactionLogs')->get();

this, give me all provinces count, but I want to filter them by their gateway_id, which gateway_id is in the transaction table

transactions

-gateway_id

transaction_logs

-provience_id

-gateway_id

proviences

-name

my relationshops:

class Provience extends Model {

public function transactionLogs ()
{
    return $this->hasMany(TransactionLog::class);
}

}

class Transaction extends Model { use HasFactory;

public function transactionLog ()
{
    return $this->hasOne(TransactionLog::class);
}

}

class TransactionLog extends Model {

public function provience ()
{
    return $this->belongsTo(Provience::class);
}

LaryAI

3 years ago

Level 58

You can use the withCount method to get the count of related models. In this case, you can use the withCount method on the Transaction model to get the count of related TransactionLog models. You can then use the whereHas method to filter the results based on the gateway_id and then use the orderBy method to order the results by the count of related TransactionLog models. Finally, you can use the get method to get the results.

$provinces = Transaction::withCount('transactionLog')
    ->whereHas('transactionLog', function ($query) use ($gateway_id) {
        $query->where('gateway_id', $gateway_id);
    })
    ->orderBy('transaction_log_count', 'desc')
    ->get();

sahar_mkr

3 years ago

Level 1

@LaryAI I want to have provinces, I mean first I want to find all provinces in the province table and then count any of them from the transaction and if there is not any province in the transaction_log table, it gives me 0, what should I do? I want to have something like:

Provience::withCount('transactionLogs')->get();

this, give me all provinces count, but I want to filter them by their gateway_id, which gateway_id is in the transaction table

nikulpaladiya

3 years ago

Level 6

@sahar_mkr try this, Hope it will work

$provinces = Provience::withCount('transactionLogs') ->whereHas('transactionLogs', function ($query) use ($gateway_id) { $query->where('gateway_id', $gateway_id); })->get();

sahar_mkr

3 years ago

Level 1

@nikulpaladiya thanks for the response, but I have error says: Column not found: 1054 Unknown column 'gateway_id' in 'where clause' (SQL: select proviences., (select count() from transaction_logs where proviences.id = transaction_logs.provience_id) as transaction_logs_count from proviences where exists (select * from transaction_logs where proviences.id = transaction_logs.provience_id and gateway_id = 1))

because gateway_id is in the transaction table

sahar_mkr

3 years ago

Best Answer

Level 1

@nikulpaladiya I found the solution at last :))

$id = 1;
 Provience::withCount(['transactionLogs' => function($query) use ($id){
                  $query->whereHas('transaction', function($innerQuery) use ($id){
                          $innerQuery->where('gateway_id', $id);
        })
	}])->get();

1 like

Please or to participate in this conversation.