Laravel get data from multiple tables

user_1091439 0
Viewed 51

I have a projects table with the value of the following column:

id_project   name           website         country_id  currency_id  timezone_id
==========.  ====           =======         ==========. ===========. ===========
1            project name.  www.yahoo.com.  10          8            10

and a countries table with the value of the following column:

id_country.      country_name
==========.      ============
10               America

and a currencies table with the value of the following column:

id_currency      currency_name
===========      ============
8                BD

and a timezones table with the value of the following column:

id_timezone      time_onze    country_name
===========      =========    ============
10               Asia/Dhaka   Bangladesh

Now, I want to run a query that should give me name, website, country_name, time_zone, currency_name. BUT you can see the country_name is exist on both table which is countries and timezones. So, the query should give me the country_name from countries table NOT time_zones table.

So, for that I am running following query:

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->get(['name', 'website', 'code', 'projects.currency_name', 'time_zone']);

return response()->json($projects, 200);

But Can't get the data

Error message:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'projects.currency_name' in 'field list' (SQL: select name, website, code, projects.currency_name, time_zone from projects left join countries on projects.country_id = countries.id_country left join currencies on projects.currency_id = currencies.id_currency left join timezones on projects.timezone_id = timezones.id_timezone)"

laravel-9 laravel
3 Answers

Try this

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->get(['projects.name', 'projects.website', 'currencies.currency_name', 'timezones.time_zone']);

return response()->json($projects, 200);

or with select helper

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->select(
['projects.name', 'projects.website', 'currencies.currency_name', 'timezones.time_zone']
)
->get();

return response()->json($projects, 200);

There is no code column in your DB. So i dont include it. Sorry for my bad english

Hope this help you

user_97862050

Actually you are doing wrong query

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->get(['projects.name', 'projects.website, 'currencies.currency_name', 'timezones.time_zone']);

return response()->json($projects, 200);

I don't see any code column can you make sure where it's comes from.

user_164324820

There's an error in your column selection, as all the column names are unique you can totally ignore adding the table name in front of columns.

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->get(['name', 'website', 'currency_name', 'time_zone']);

return response()->json($projects, 200);
user_77125650
Related