Support the ongoing development of Laravel.io →
posted 11 years ago
Database

Hi,

This one is completely SQL question.

Assuming we have the next query:

SELECT *, (SELECT COUNT(id) FROM (SELECT * FROM `mozenda_logs` WHERE bank_account_id=bank_accounts.id ORDER BY `created_at` DESC) AS tmp WHERE `status` != 'Done' AND `created_at` > '2014-05-27 00:00:00' AND `created_at` < '2014-05-28 00:00:00' AND bank_account_id=bank_accounts.id) AS count_mozenda_error_logs FROM `bank_accounts`

Could someone tell me if it's possible to have it working, meaning that we can somehow pass the general bank_accounts.id value to the most inner sql query.

The idea is that I have the main table "bank_accounts" and the other table where I store the logs for the certain bank_accounts. I need to do the query which would return all the bank_accounts and a column which represents the number of unsuccessful log queries from the last 3 queries for that bank. Log is unsuccessful if status != 'Done'.

I need to have such difficult inner query structure, because I want to store that column functionality in the Laravel scope.

Currently the query doesn't work because that inner query doesn't know anything about the bank_account table.

Would be happy to receive any thoughts.

Last updated 3 years ago.
0

Isn't the problem just that bank_acount_id is ambiguous?

Last updated 3 years ago.
0

No, the problem is:

Unknown column 'bank_accounts.id' in 'where clause'

Last updated 3 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2025 Laravel.io - All rights reserved.