Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

Thanks for clearing up my code snippet!

I don't think the orWhere would help me much though. I need to return all contacts where the name is a match and where their agency is a match as well, which is the related model.

Update: Yes, in contact table, their is a field: agency_id

Last updated 2 years ago.
0

With Edit 2, if I use the "with", I still need to define the condition that give me all contacts where the user has an agency and the agency name is like $something. What is odd, is that in my original post, you can see that the debug query it displays, when ran in sql, give me 1 result, which is what I'm expecting. But when laravel runs it, i'm getting all contacts that have the agency name that is matched.

Last updated 2 years ago.
0

So I changed the query to the above, but noticed that in the debug query, the 2 parts of the query are not wrapped in "and", so i was checking for either agency name equal or contact name equals.

I changed it to:

$data = Contact::whereHas('agency', function($q) use ($agency) {
              $q->where('name', 'like', "%$agency%");
            })
              ->where(function($q) use ($string) {
                $q->where('name', 'like', "%$string%")
                ->where('job_title', 'like', "%$string%", 'OR')
                ->where('location', 'like', "%$string%", 'OR')
                ->where('address', 'like', "%$string%", 'OR');
            });

However, I'm still getting back all contacts where the agency name was a match, but the debug query is producing this, and when it's ran in Navicat, returns just one contact, which is what I would expect.

select * from `contacts` where (select count(*) from `agencies` where `contacts`.`agency_id` = `agencies`.`id` and `name` like '%active%') >= 1 and (`name` like '%adam%' OR `job_title` like '%adam%' OR `location` like '%adam%' OR `address` like '%adam%') order by `updated_at` desc limit 100 offset 0
Last updated 2 years ago.
0

Sure, here is the debug query:

[2014-07-18 11:53:07] local.ERROR: select * from `contacts` where (select count(*) from `agencies` where `contacts`.`agency_id` = `agencies`.`id` and `name` like ?) >= 1 and `name` like ? or `job_title` like ? or `location` like ? or `address` like ? order by `updated_at` desc limit 100 offset 0

On the php side of things, I'm getting over 30+ contacts in the response.

And as usual, if I take the debug query from above and run it in navicat, I get back just 1 result, which is what I'm wanting based on the query params for the agency name and contact name.

Last updated 2 years ago.
0

K, got it working. This is the query:

$data = Contact::whereHas('agency', function($q) use ($agency) {
              $q->where('name', 'like', "%$agency%");
            })
            ->where(function($q) use ($string) {
              $q->where('name', 'like', "%$string%")
              ->where('job_title', 'like', "%$string%", 'OR')
              ->where('location', 'like', "%$string%", 'OR')
              ->where('address', 'like', "%$string%", 'OR');
            });

Now, onto the embarrassing part. I was setting string to the param being passed in. However, I just noticed in the debug query, that it was always a empty like "%%". I was grabbing Input::get('keyword'), but the param was "keywords"

Everything is working now. Appreciate your help on this!

Last updated 2 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

whnunlife whnunlife Joined 23 Jun 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.