Laravel 4 relational database query

Question

can anybody help me on the following query.

I have a table that holds a postcode column and a rating column, ie.

ID |  POSTCODE | RATING 
 1 |   sk101dd |   E
 2 |   sk101de |   A
 3 |   sk101df |   E
 4 |   sk101dg |   E
 5 |   sk101dh |   D    
 etc

This is set up as a model called PostcodeList

I have a relational table, linked via the RATING column that holds a customer id and cost, ie.

ID | CUSTOMER_ID | RATING | COST
 1 |    1234     |    E   | 0.05
 2 |    9999     |    E   | 0.02

This is set up as a model called RatingCost. I linked this to PostcodeList model using the following code:

public function costing()
{
    return $this->hasMany('PostcodeList','RATING','RATING');
}

I need to return the COST from the RatingCost model using CUSTOMER_ID as the filter without resorting to multiple sql statements. I think I've nearly got it, using the code below, but it's not quite right:

$p = PostcodeList::where('postcode',$somepostcode)->first();

$cost = $p->costing()->where('customer_id',$somecustomerid)->first()->cost;

The error I'm getting at the moment is "Trying to get property of non-object".

Any help greatly appreciated. I don't want to resort to DBRAW or another form of join as I really like the relational setup Laravel provides.

thanks

It looks like you're getting the error because postcode does not have the customer_id column. You should also tag this as php — dev_feed
– dev_feed, Commented Feb 6, 2014 at 20:28
Thanks for the reply. The postcode table doesn't store any customer id's, only the RatingCost table holds this as there will be multiple different customer id's linked to the same rating but with different costs. So I'm probably explaining that incorrectly to Laravel but can't figure out the correct code — Delmontee
– Delmontee, Commented Feb 6, 2014 at 20:31
That makes more sense. So why do you iterate the postcode list to match the customer id? I'm not familiar with DBRAW but laravel does implement its own joins here: laravel.com/docs/queries#joins — dev_feed
– dev_feed, Commented Feb 6, 2014 at 20:43
I need to search through the PostcodeList to find the entered postcode ($somepostcode). Actually I could simplify as I don't need to use get(), just first(). Will amend that bit now. — Delmontee
– Delmontee, Commented Feb 6, 2014 at 20:47

dev_feed · Accepted Answer · 2014-02-06 20:47:37Z

1

I know you're trying to stay away from joins, but this Laravel query would produce the desired results:

  DB::table('PostcodeList')
            ->join('RatingCost', function($join)
            {
                $join->on('RATING', '=', 'RatingCost.RATING')
                     ->->where('customer_id',$somecustomerid)
            })

answered Feb 6, 2014 at 20:47

dev_feed

7192 gold badges8 silver badges28 bronze badges

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

3 Comments

Delmontee Over a year ago

Thanks, yeah that was my backup plan, and the one I'll go for if I can't figure it out. But I'd love to get the hasMany bit working if possible.

dev_feed Over a year ago

Is it possible that the error stems from the local key and foreign key both being named 'RATING'?

Delmontee Over a year ago

the actual code doesn't use the same key names I've used here, I amended for clarity. But thanks for the response.

The Alpha · Accepted Answer · 2014-02-06 21:04:14Z

1

You have this

$postcode_get = PostcodeList::where('postcode',$somepostcode)->get();
foreach($postcode_get as $p){
   ...
   $cost = $p->costing()->where('customer_id',$somecustomerid)
   // ...
}

You have defined the method costing in your RatingCost model but calling it from PostcodeList model, in this case you need to declare the relation inside your PostcodeList model like this:

public function costing()
{
    // change the field name 'RATING' in any table, maybe
    // prefix with table name or something else, keep different
    return $this->belongsToMany('RatingCost','RATING', 'RATING');
}

So, you can use this (inside loop):

$cost = $p->costing();

Because, inside your loop each $p represents a PostcodeList model and $postcode_get is a collection of PostcodeList models.

edited Feb 6, 2014 at 21:04

answered Feb 6, 2014 at 20:52

The Alpha

147k30 gold badges294 silver badges313 bronze badges

3 Comments

Delmontee Over a year ago

Thanks for the reply. I originally used "belongsTo" exactly as you've stated there. However there may be multiple costs depending on the customer_id, ie customer with id 1234 types in postcode 'sk101dd' and it returns rating E with cost 0.05, however customer with id 9999 types in postcode 'sk101dd' and it returns rating E with cost 0.02. Am I right in thinking this will therefore require a "hasMany" rather than a "belongsTo"?

The Alpha Over a year ago

It should be then belongsToMany instead of belongsTo if I get your point.

Delmontee Over a year ago

Thanks, doesn't seem to fix the problem though. I'm still trying some things, will update post when I get somewhere

Delmontee · Accepted Answer · 2014-02-06 22:35:38Z

0

I don't think what I'm trying to do is actually possible without using joins. So the solution was to scrap the belongsTo and hasMany options for a standard join (similar to dev_feed's response):

$pr = PostcodeList::join('RatingCost', function($join)
      {
         $join->on('PostcodeList.content_rate', '=', 'RatingCost.code');
      })
      ->where('postcode', '=', $somepostcode)
      ->where('RatingCost.customer_id','=',$somecustomerid)
      ->first();

answered Feb 6, 2014 at 22:35

Delmontee

2,4343 gold badges36 silver badges59 bronze badges

3 Comments

dev_feed Over a year ago

glad you figured it out man. please accept your answer so that when others view the question, they see your answer at the top

Delmontee Over a year ago

Yep, how do I set as answered? Can't find any other option

dev_feed Over a year ago

see this link to learn a little bit more about it

Collectives™ on Stack Overflow

Laravel 4 relational database query

3 Answers 3

3 Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related