CODEWITHSUNDEEP
laravellaravel-4
ehp
ehp

Reputation: 2565

Laravel query builder

I am new to Laravel. Can anyone tell me how should I write this query in laravel query builder ?

SELECT users.id, customers.zip
FROM users, customers
WHERE users.id =26
AND customers.customer_id = users.user_name

Upvotes: 0

Views: 269

Answers (4)

gaurav coding HUB
gaurav coding HUB

Reputation: 11

DB::table('users as u1') ->join('customers as c1', 'c1.customer_id', '=', 'u1.user_name') ->->where('users.id', '=', 26) ->select('users.id', 'customers.zip');

Upvotes: 0

Al Ameen
Al Ameen

Reputation: 372

DB::table('users')
->join('customers','users.username', '=', 'customer.customer_id')
->select('users.id', 'customer.zip')
->where('users.id','=', '26')
->get();

this will for you.

refer this for more https://laravel.com/docs/5.2/queries

if you want to use eloquent collections refer this https://laravel.com/docs/5.2/collections#method-filter.

Upvotes: 1

user34038
user34038

Reputation: 19

Well In your case is better to use HasOne relationship http://laravel.com/docs/eloquent#relationships You need to create Customer class

 class Customer extends Eloquent {

   //if you table is not called customers you need to add
    protected $table = 'table_name';

    }

Than in User controller

class User extends Eloquent {

        public function customers()
        {
            return $this->hasOne('Customer', 'customer_id');
        }

    }

In a nutshell laravel will find users.id(26) and join them with customer_id(26), it's easier and simpler less code in controller.

$user_id = 26; 
$user = User::find($user_id);

echo "username:".$user->user_name."user/customer zip code: ".$user->customers->zip;

This relationship is only if each user has one record in customers table(so if there will be twoo user_id 26 it will get only the first) if you want to have them more then one record for each user you need to use hasMany

Upvotes: 0

petercoles
petercoles

Reputation: 1822

Simplest method is with the query builder - here's a link to a join example

As you develop your knowledge of Laravel, you'll likely start using Laravel's Eloquent ORM, in which case you'll want to read this section of the docs to learn how to define relationships between tables.

Update:

I've see your comment above above, about not being able to understand the docs. That's surprising because they're well-tested and clear in this area, but here's answer I think you're looking for:

DB::table('users')
    ->where('users.id', '=', 26)
    ->join('customers', 'customers.customer_id', '=', 'users.user_name')
    ->select('users.id', 'customers.zip');

Upvotes: 1

Related Questions