CODEWITHSUNDEEP
mysqlsqldatabaselaravel
Guru
Guru

Reputation: 327

How to get details of user id from multiple column conditions?

I have a users table, i need to retrieve address,phone and email of a user where columns having address_type = 1, phone_type=1,email_type=1. ('_type=1' means present user using details and others are unused and archived records)

Below is my table structure

user table

From above table i want to get address,phone,email of user_id=1 and where conditions are address_type=1, phone_type=1,email_type=1

Any help would be great..

Upvotes: 3

Views: 631

Answers (4)

user3601546
user3601546

Reputation: 318

You can do this with the following query:

$user = Model::where('user_id', $userId)
    ->where('address_type', 1)
    ->where('phone_type', 1)
    ->where('email_type', 1)
    ->first();

Note: replace Model with the name of your model.

More info about Eloquent: https://laravel.com/docs/5.4/eloquent#retrieving-models

Upvotes: 0

Alexey Mezenin
Alexey Mezenin

Reputation: 163768

You can use Eloquent for that. Load the data with one simple query:

$data = Model::where('user_id', $userId)->get();

And then get an address, a phone number and an email from this collection:

$address = $data->where('address_type', 1)->first()->address;
$phone = $data->where('phone_type', 1)->first()->phone;
$email = $data->where('email_type', 1)->first()->email;

Upvotes: 1

Guru
Guru

Reputation: 327

I got the below solution by making query in column selection itself as below

SELECT DISTINCT user_id, (SELECT address from tsets where address_type IN (1)) as address, (SELECT email from tsets where email_type IN (1)) as email FROM tsets

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use conditional aggregation:

select id,
       max(case when address_type = 1 then address end) as address,
       max(case when phone_type = 1 then phone end) as phone,
       max(case when email_type = 1 then email end) as email
from t
where id = 1
group by id;

If there can be multiple values with "1", then use group_concat() instead of max() to get all of them.

Upvotes: 0

Related Questions