CODEWITHSUNDEEP
laraveldatabase-design
Darryl Hardin
Darryl Hardin

Reputation: 137

Trying to retrieve names from 'name column' from two different tables

I am trying to pull the names columns from the categories table and the brands table. The brand_user table is the bridge/pivot table between the brands and users so I can pull brands based on a user's id.

The problem is my output only gives the categories name column in the results and the brands name is omitted. I've tried doing left and right joins and the issue persists. It doesn't seem I should have to call the brands name column brand_name and the categories name column category_name, but at this moment that is the only solution I can think of but would rather not if possible (and if it makes sense to not).

So my question is, should I make the name columns unique to each table, or can I use name as the generic name column for tables that would use it?

return DB::table('brands')
            ->join('categories', 'categories.id', '=', 'brands.category_id')    
            ->join('brand_user', 'brand_user.brand_id', '=', 'brands.id')
            ->select('brands.name', 'categories.name')
            ->where('user_id', Auth::user()->id)
            ->get();

Upvotes: 0

Views: 41

Answers (2)

Anurat Chapanond
Anurat Chapanond

Reputation: 2987

In case you are interested in Eloquent and Laravel way and you already have a many to many relationship between brand and user and one to many relationship between brand and category in place, you can iterate through brands and categories this way.

$brands = Brand::whereHas('users', function($query) {
        $query->where('users.id', auth()->id());
    })
    ->with('category')
    ->get();

foreach($brands as $brand) {
    echo $brand->name .' ' .$brand->category->name;
}

You can look for more details of Eloquent relationship here https://laravel.com/docs/8.x/eloquent-relationships

Upvotes: 1

Darryl Hardin
Darryl Hardin

Reputation: 137

It appears I can just add an alias for those and it takes care of the ambiguous column name issue.

return DB::table('brands')
            ->join('categories', 'categories.id', '=', 'brands.category_id')    
            ->join('brand_user', 'brand_user.brand_id', '=', 'brands.id')
            ->select('brands.name as bName', 'categories.name as cName')
            ->where('user_id', Auth::user()->id)
            ->get();

Upvotes: 0

Related Questions