query with distinct and order by

I would try it without distinct since it only should be used as a last option due to performance issues.

Try this first to see if it gets the result you want.

DB::raw("SELECT city FROM conferences WHERE city LIKE '%$city%' ORDER BY start_date ASC");

If not try with distinct.

DB::raw("SELECT DISTINCT city FROM conferences WHERE city LIKE '%$city%' ORDER BY start_date ASC");

Thanks, but also dont works, this is for a autocomplete search. It shows on console "jquery.min.js:4 GET https://proj.test/autocomplete-search?term=new 500 ()".

I forgot to wrap the '%$city%' in single quotes.

Thanks, but same error with " $cities = DB::raw("SELECT DISTINCT city FROM conferences WHERE city LIKE '%$search%' ORDER BY start_date ASC"); ".

what does the log file say?

On the console it shows "jquery.min.js:4 GET https://proj.test/autocomplete-search?term=new 500 ()".

That's not your error log.

It shows "General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'proj.conferences.start_date' which is not in SELECT list; this is incompatible with DISTINCT (SQL: select distinct city from conferences where city LIKE %new% order by start_date asc)".

So you need to add start_date in the fields that you are getting. Right now, you are telling it to ONLY get the city column here ->get(['city']), but then you're telling it to sort by start_date, but you didn't select it.

My, those logs are useful, eh?

DB::raw("SELECT DISTINCT city, start_date FROM conferences WHERE city LIKE '%$city%' ORDER BY start_date ASC");

Thanks, but like this:

$cities = Conference::where('city', 'LIKE', '%'.$search.'%')->distinct()->orderBy('start_date', 'asc')->get(['city','start_date']);

The distinct is not working, it appears multiple times for example "Newcastle".

With

DB::raw("SELECT DISTINCT city, start_date FROM conferences WHERE city LIKE '%$city%' ORDER BY start_date ASC");

dont appear any error but if the user introduces for example "New" the city "Newcastle" dont appear, no city appears.

"SELECT city, min(start_date) FROM conferences WHERE city LIKE '%$city%' GROUP BY city ORDER BY min(start_date) ASC"

Something like that will give you the lowest tart_date for each city.

Thanks, but also dont shows nothing, if the user introduce for example "New" dont appears Newcastle with that query. But it appears with only " $cities = Conference::where('city', 'LIKE', '%'.$search.'%')->distinct()->get(['city']); ".

Try this one

"SELECT city, min(start_date) FROM conferences WHERE city LIKE '%New%' GROUP BY city ORDER BY min(start_date) ASC"

Does it give you Newcastle?

And try this one

"SELECT city, min(start_date) FROM conferences WHERE city LIKE '%" . $city . "%' GROUP BY city ORDER BY min(start_date) ASC"

Thanks, but also dont show.

What does the table look like and what data is in it? Are you running the query properly?

Show the code where you run the query.

Code:

public function search(Request $request){
    $search = $request->term;
    $conferences = Conference::where('name', 'LIKE', '%'.$search.'%')->get();

    //$cities = Conference::where('city', 'LIKE', '%'.$search.'%')->distinct()->get(['city']);
    $cities = DB::raw("SELECT city, min(start_date) FROM conferences WHERE city LIKE '%" . $search . "%'
     GROUP BY city ORDER BY min(start_date) ASC");
    $data= [];
    foreach ($conferences as $key => $value){
        $data[] = ['category'=> 'Conferences', 'value' => $value->name, 'url' => 'conference/'.$value->id.'/'.$value->slug];
    }
    foreach ($cities as $key => $value){
        $data[] = ['category'=> 'Cities', 'value' => $value->city, 'url' => 'conferences/where/city/'.$value->city];
    }
    return response($data);
}

The table is like:

ID    name                  start_date                 city            ..more fields
   
1      Conference  1    2018-08-10 15:30:00   Newcastle

The SQL is sound so if you do a dd($cities); you will see the result you expect. It's somewhere else in that mess of code that I have no clue of what you are trying to do with that messes the whole thing up.

Do a dd($request); to just to see that you have a termthere.

What is it you want to have in your $data?

The search() method is the method for the autocomplete jquery ui search. For example, when the user enter 3 letters in the autocomplete search field it appears the results.

For example if the user introduce 3 letters for a conference name like "con" it appears "conference 1", "Conference 2", etc.

If the user introduces the name of a city like "New" it appears "Newcastle".

The $request when the user introduces for example "New" is:

Request {#43 ▼
  #json: null
  #convertedFiles: []
  #userResolver: Closure {#236 ▶}
  #routeResolver: Closure {#238 ▶}
  +attributes: ParameterBag {#45 ▶}
  +request: ParameterBag {#51 ▶}
  +query: ParameterBag {#51 ▶}
  +server: ServerBag {#47 ▶}
  +files: FileBag {#48 ▶}
  +cookies: ParameterBag {#46 ▶}
  +headers: HeaderBag {#49 ▶}
  #content: null
  #languages: null
  #charsets: null
  #encodings: null
  #acceptableContentTypes: null
  #pathInfo: "/autocomplete-search"
  #requestUri: "/autocomplete-search?term=new"
  #baseUrl: ""
  #basePath: null
  #method: "GET"
  #format: null
  #session: Store {#303 ▶}
  #locale: null
  #defaultLocale: "en"
  -isHostValid: true
  -isForwardedValid: true
  basePath: ""
  format: "html"
}

The $data returned shows with "$cities = Conference::where('city', 'LIKE', '%'.$search.'%')->distinct()->get(['city']);":

[{category: "Cities", value: "Newcastle", url: "conferences/where/city/Newcastle"}]
0
:
{category: "Cities", value: "Newcastle", url: "conferences/where/city/Newcastle"}

With " $cities = DB::raw("SELECT city, min(start_date) FROM conferences WHERE city LIKE '%" . $search . "%' GROUP BY city ORDER BY min(start_date) ASC");" shows:

[]
No properties

er guys

DB::raw("SELECT city FROM conferences WHERE city LIKE '%$city%' ORDER BY start_date ASC");

is REALLY bad practice. NEVER insert user supplied variables into SQL strings.

I cannot really believe I am seeing this.