Laravel get data from multiple tables

Question

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)"

You should use currencies.currency_name instead of projects.currency_name because you have currency_name in currencies table not projects table. — Muhammad Hassan Javed
– Muhammad Hassan Javed, Commented Jun 22, 2022 at 7:04

Md Atiqur · Accepted Answer · 2022-06-22 07:07:27Z

2

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.

answered Jun 22, 2022 at 7:07

Md Atiqur

7338 silver badges15 bronze badges

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

Comments

Hafid Maulana · Accepted Answer · 2022-06-22 07:06:19Z

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

Muhammad Hassan Javed · Accepted Answer · 2022-06-22 07:10:27Z

0

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);

answered Jun 22, 2022 at 7:10

Muhammad Hassan Javed

3903 silver badges16 bronze badges

7 Comments

Shibbir Over a year ago

Do you think above query will work?

Shibbir Over a year ago

if I use the table name before the column then it's working.

Muhammad Hassan Javed Over a year ago

Yes, as long as column names are unique. None of the name should exist in other tables.

Muhammad Hassan Javed Over a year ago

Then you might have same column name in other tables as well.

Muhammad Hassan Javed Over a year ago

The table structure you posted in post is complete or partial?

|

Collectives™ on Stack Overflow

Laravel get data from multiple tables

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related