Database schem / relations help

What type of listings are these? In general I would look at the relationships like this:

1. a Listing belongsTo a City, a City hasMany Listings
2. a Listing belongsTo a Sub_Category, a Sub_Category hasMany Listings
3. a Sub_Category belongsTo a Category, a Category hasMany Sub_Categories

Does that make sense? Is that what you were looking for, or did I misunderstand the question? :)

Hi Mike!

That is exactly where I am right now, thank you very much for your answer :). Later today I will see if it actually works the way I want it to.

I made a pivot table to manage the relationship between listings and subcategories, is that the right way to do this?

you only need pivot table for m:n relationships(listing can have many subcategories and subcategory can have many listings)

Hey Rudamel, So for the relationship between the categories table and the sub_categories table, i would just make a standard foreign key constraint with category_id? :)

Thanks!

Just think of it logically, would one table belong to many rows from another table. Or would a row only have one from another table.

Write down that then take a look at the relationships noted in the docs, you'll soon work out which would have what http://laravel.com/docs/eloquent#relationships

I suggest using this to create your pivot migrations as it saves a lot of hassle https://github.com/JeffreyWay/Laravel-Generators

Hey Bashy, Yea, I already used that package for this - it's so helpful!

I think I confused myself with the listings and where these should belong in the first place!

If a subcategory belongs to only one category, then yes you only need to add category_id on your subcategory table .)

Stupid question, now that i've set up my database.

How do i show listings associated with my subcategories table?

I define how listings belong to the different subcategories through the pivot table, but how can I query this?

Doing

return = Subcategory::with('listings')
    ->get();

Does not work?

$listings = Subcategory::find(1)->listings; this will select all listings associated with subcategory with id 1(assuming you have correctly defined relations on your models)

Obviously I have done something wrong then :), cause i'm getting this error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'listings.subcategory_id' in 'where clause' (SQL: select * from `listings` where `listings`.`subcategory_id` = 1)

Subcategory model:

class Subcategory extends \Eloquent {
  protected $fillable = ['name'];

  public function listings() {
    return $this->hasMany('Listing');
  }

  public function category()
  {
    return $this->belongsTo('Category');
  }
}

Listings model:

<?php

use Carbon\Carbon;

class Listing extends \Eloquent {
  protected $fillable = ['name', 'category_id'];

  public function city() 
  {
     return $this->belongsTo('City', 'city_id');
  }

  public function subcategory() 
  {
    return $this->belongsTo('Subcategory');
  }

}

Am I missing something here?

you have defined one to many relation between subcategory and listing in your models, but if you linked them together via pivot table that is many to many realtion so you would use belongsToMany on both subcategory and listing model. Also why does listing belongs to city. I'm not sure that I understand your schema right now ;D

My understanding was that the listings had to belong to the specific city, so that the user is only shown listings from the specific cities, matching category and subcategory :)!

Hmm, does not work.

I did the following:

Listing model:

  public function subcategory() 
  {
    return $this->belongsToMany('Subcategory');
  }

Subcategory model:

  public function subcategories() {
    return $this->belongsToMany('Subcategory');
  }

Still get this:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'listings.subcategory_id' in 'where clause' (SQL: select * from `listings` where `listings`.`subcategory_id` = 1)

This is how your table should look like:

Listings:
id
sub_category_id
city_id

Sub_categories:
id
category_id
name

Categories:
id
name

Cities:
id
name

So one listing can only have one Sub_category, one Category (trough the Sub_category) and one city.

So fallowing your user stories the fallowing queries would be executed in order:

$category_id = 1;

$result = Listing::whereCityId($city_id)
    ->whereHas('Sub_category', function($q) use ($category_id) {
        $q->whereCategoryId($category_id);
    })
    ->get();

$category_id = 1;
$sub_category_ids = [2, 3, 4];

$result = Listing::whereCityId($city_id)
    ->whereIn('Sub_category_id', $sub_category_ids)
    ->get();

Hi ilyes512 ! Thanks for your answer! And what about the pivot table, would that still persist?

Not sure what you want. To me a category is a one to many relation. So that means a listing only belongs to 1 category. If you want it to be able to belong to multiple categories then it's more like a "tag"? In that case you would need a pivot table.

If you want the listings to belong to multiple cities, then you would also need a pivot table for that. If a listing is only ment for one city then you don't need any.

well yea, im sorry if its a bit unclear what i actually want to achieve.

A listing has to be unique, and can only belong to one category and one city, so that the user can specify his/her search once they have chosen a top-category and a city.

User choses city and top category Then they are shown all listings according to these choices, and then they specify further (if they want to) using the sub categories.

Top category could be: Shopping Attractions

Sub category could be: Clothing Museums

Hope it makes sense :)

Yes, then the table structure as I given it should work and you wont need any pivot tables.

Coming from the WP world and how they handle categories to me it seems like you only need one table for categories. One for Listings and one for City.

In the Category table you simply have a parent_id column, this references another category ID in the table. Have it default 0 if it's a parent category. When you create a "sub" category just link it to the parent. Since the sub cats can only have one parent you don't really need a pivot table for it. In eloquent I'd just create a method that gets the current ID, and spins through the DB looking for any cats with it's ID.

class Category extends Eloquent {

    public function getChildCategories() {
        return Category::where('parent_id', $this->attributes['id'])->get();
    }

}

To me atleast that is how I would handle the category side of things.

Then just roll over it like:

foreach($category->getChildCatetories() as $child) {}

Hey guys! Just thought I would get back and thank you all for helping me solve this issue, it surely has caused a lot of headache over the past few days for me, but it seems that with your help I am there 95% now :).

I have one last issue. Right now the listings shown are dependent on the city_id and the subcategory_id.

How do I make it so that it also depends on the category_name in the query?

My route is:

Route::get('/annoncer/{city_id}/{category_name}/{subcategory_id}', [
  'as' => 'searchbycat',
  'uses' => 'ListingsController@showBySubCat']
  );

My function:

  public function showBySubCat($city_id, $category_name, $subcategory_id) {

   $category = Category::wherename($category_name)->firstOrFail();

   $listings = Listing::where('city_id', '=', $city_id)
    ->where('subcategory_id', '=', $subcategory_id)
   ->get();

  return View::make('cities.cities-view', compact('categories', 'listings'));

 }

/annoncer/7100/Shopping/3 <- is what is being produced now.

Ideally it should be /annoncer/7100/Shopping/(name_of_subcategory) - and it should be dependent on the category_name as well as the others (which works right now).

Hope it makes sense, and thanks again! :)

I'm trying to do something similar. The way bgallagh3r says looks cleaner, and I guess it works with as many subcategory levels as you wish.

I posted a new question related to this post: goo.gl/qAKgAi