Query returns wrong value

I'm trying to validate an excel file, in this case I'm trying to search between the excel row $data and the system information to verify it's correct. Here I'm trying to find districts, these districts have a chained relationship like this country->province->canton->district. What is happening is that I have multiple districts with the same exact name but different cantons, the search is bringing the wrong one and isn't finding the one I need.

Here is the query

if ($data && array_key_exists('uuid', $data)) {
	$gpsSalePoint = GpsSalePoint::with('district')->where('uuid', $data['uuid'])->first();

$countryIds = Country::query()
                        ->whereRaw('lower(name) like ?', [Str::of($data['pais'])->squish()->lower()->value()])
                        ->pluck('id');

                    if (!$countryIds->contains(tenant('country_id'))) {
                        //not the right country
                    }

                    $provinceIds = Province::query()
                        ->whereRaw('lower(name) like ?', [Str::of($data['provincia'])->squish()->lower()->value()])
                        ->where('country_id', tenant('country_id'))
                        ->pluck('id');

                    if ($provinceIds->count() === 0) {
                       //this province does NOT belong to the country
                    }

                    $cantonIds = Canton::query()
                        ->whereRaw('lower(name) like ?', [Str::of($data['canton'])->squish()->lower()->value()])
                        ->whereIn('province_id', $provinceIds)
                        ->pluck('id');

                    if ($cantonIds->count() === 0) {
                         //this canton does NOT belong to the provinces
                    }

 $districtIds = District::query()
                        ->whereRaw('lower(name) like ?', [Str::of($data['distrito'])->squish()->lower()->value()])
                        ->whereIn('canton_id', $cantonIds)
                        ->get('id');

                    if ($districtIds->count() === 0) {
                        //this district does NOT belong to the cantons
                    }
}

The problem is with $districtIds if I remove lower() from [Str::of($data['distrito'])->squish()->lower()->value()] then I get an array with the correct id in it but it's not just the one like I expect. I assumed that the $districtIds whereRaw(lower(name)) would complement the lower() and make everything lowercase but now I'm not sure since it's not matching.

If I remove the whereIn I see two results one is correct while the other is not, however for some reason it always picks the wrong one and when I put the whereIn back it now returns empty since the one it found is not in the proper cantons.

These are the values in my db with the same exact name, the only half acceptable answers are the first two because the last one is in a different country, the one I need is the first one with canton_id as 22.

id, name, code, canton_id
137	SAN RAMÓN	20201	22
261	SAN RAMÓN	30307	38
558	San Ramón	1412	92

I'm very confused what is going on and why it doesn't return the correct value, what am I doing wrong? The rest work well but maybe there is something I'm overlooking?

iamgeorge

1 year ago

Level 31

Try this

if ($data && array_key_exists('uuid', $data)) { $gpsSalePoint = GpsSalePoint::with('district')->where('uuid', $data['uuid'])->first(); $countryIds = Country::query() ->whereRaw('lower(name) like ?', [Str::of($data['pais'])->squish()->lower()->value()]) ->pluck('id');

if (!$countryIds->contains(tenant('country_id'))) {
    // Handle country mismatch
}

$provinceIds = Province::query()
    ->whereRaw('lower(name) like ?', [Str::of($data['provincia'])->squish()->lower()->value()])
    ->where('country_id', tenant('country_id'))
    ->pluck('id');

if ($provinceIds->count() === 0) {
    // Handle province mismatch
}

$cantonIds = Canton::query()
    ->whereRaw('lower(name) like ?', [Str::of($data['canton'])->squish()->lower()->value()])
    ->whereIn('province_id', $provinceIds)
    ->pluck('id');

if ($cantonIds->count() === 0) {
    // Handle canton mismatch
}

$districtIds = District::query()
    ->whereRaw('lower(name) like ?', [Str::of($data['distrito'])->squish()->lower()->value()])
    ->whereIn('canton_id', $cantonIds)
    ->pluck('id');

if ($districtIds->count() === 0) {
    // Handle district mismatch
} else {
    $district = District::find($districtIds->first());
    if ($district && $district->canton_id == 22) {
        // Process the correct district
    } else {
        // Handle incorrect district or canton_id
    }
}

}

Snapey

1 year ago

Level 122

You probably have a Case Insensitive (CI) database collation so letter case (and use of lower) should be irrelevant.

I note you are using 'like' but not providing any wildcards?

eggplantSword

1 year ago

Level 9

@Snapey I think that's exactly what it is, these models aren't for regular tables but a sqlite thing, so I think my problem are special characters since my names are in Spanish. I reworked the queries so it's all good now

Please or to participate in this conversation.