How to select all records with relation and where clause Laravel MySQL

Question

I'm new to Laravel so I have a bit of a problem. I have tables 'Categories' and 'Products' in my DB. In my models I setup the relations like:

Product.php:

public function category() 
{
    return $this->belongsToMany(Category::class);
}

Category.php:

public function products() 
{
    return $this->hasMany(Product::class);
}

What I need now is that I want to select all of the Categories with their related Products. (User enters in search bar category name and gets list of Categories and when user selects Category I get all of the columns from Categories and ALSO Products related with this Category).

I have tried something like this:

public function findCategory(Request $request)
{
    return Category::with('products')
        ->where('name', 'like', '%' . $request->category_name . '%')
        ->limit(15)
        ->get();
}

ALSO:

public function findCategory(Request $request)
{
    return Category::where('name', 'like', '%' . $request->category_name . '%')
        ->products()
        ->limit(15)
        ->get();
}

But this doesn't seem to work and I ran out of ideas. Does anyone know if there is a way to do this? Any help would be much appreciated :)

in product collection are you searching with where or from category? — Ritesh Khatri, Nov 16 '17 at 11:24
Not sure if I understood your question. But I don't need ' where' in products collection. The where clause should be on category. Basically like Category::where('name', 'like', '%' . $request->category_name . '%') and this works just fine, but i also want to get products related to specific category — ineffable, Nov 16 '17 at 11:29
yes, right now i get to my vue component data- list of categories (in which i have columns like id, name etc) and i also want an array of products which are related to this specific category — ineffable, Nov 16 '17 at 11:36
see this i hope it help you -> [link](https://stackoverflow.com/questions/36208460/hasmany-vs-belongstomany-in-laravel-5-x) — Ritesh Khatri, Nov 16 '17 at 11:54
ok, changed to belongsToMany in Product, and I do have a pivot table called product_category with foreign keys 'id' (which is a product id) and category_id, also i created a model ProductCategory (not sure what relations should be there), and I have a ProductCategoriesController in which i have this method findCategory — ineffable, Nov 16 '17 at 12:01
for many to many relation for the parent `belongsToMany` as well as for child we have to use `belongsToMany`. — Ritesh Khatri, Nov 16 '17 at 12:14
If you need reference go through documentation [link](https://laravel.com/docs/5.5/eloquent-relationships) Many to Many topic. — Ritesh Khatri, Nov 16 '17 at 12:15
nothing happened, unfortunately it still doesn't work, even though i changed it. I used solution below but still the same error "Error: Request failed with status code 500" — ineffable, Nov 16 '17 at 12:16
I also searching some stuff for you brother to point you exactly the concept of this relation. — Ritesh Khatri, Nov 16 '17 at 12:40

aimme · Answer 1 · 2017-11-16T11:50:17.300

0

it would be something like

public function findCategory(Request $request)
{

    $term = $request->category_name;

    return Category::with('products')->where(function($q) use($term) {
               $q->where('categories.name','like',"%$term%");
            })->paginate(15); //or use limit(15)


}

edited Nov 16 '17 at 11:50

answered Nov 16 '17 at 11:09

aimme

6,385
7
48
65

Oh but wait, doesn't the where clause search in relation? (where is executed on products instead of categories?) Cause, unfortunately, it doesn't seem to work. I'm using axios to get results (in my vuejs component) but it just responds with "Uncaught (in promise) Error: Request failed with status code 500" :( – ineffable Nov 16 '17 at 11:25
oh i am sorry. mistyped it. check now. – aimme Nov 16 '17 at 11:50

score 0 · Answer 2 · answered Nov 16 '17 at 13:46

0

Your query is almost complete except that you didn't load related model. Therefore, you last example should be:

public function findCategory(Request $request)
{
    return Category::where('name', 'like', '%' . $request->category_name . '%')->
      ->with('products') //load related products
      ->limit(15)
      ->get();
}

answered Nov 16 '17 at 13:46

Oluwatobi Samuel Omisakin

6,944
3
33
48

i think i have tried that and it didn't work, so i think i must have some kind of mistake in my model – ineffable Nov 16 '17 at 13:54
hmm run the query with the conditions and see if you still get any result – Oluwatobi Samuel Omisakin Nov 16 '17 at 14:24

score 0 · Answer 3 · answered May 21 '21 at 04:37

0

public function getAllData()
{
    $data = tableName::where('Method','Delivery')->get();
    return response()->json($data, 200);
}

OR

$users = DB::table_Name('users')->select('name', 'email as user_email')->get();

answered May 21 '21 at 04:37

Menisha Myelwaganam

254
3
26

1

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – mufazmi May 21 '21 at 18:16

How to select all records with relation and where clause Laravel MySQL

3 Answers3