Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have gone into this strange issue, I exported the database from the staging server to local and just import it, and boom it is showing data, but in the case of the staging server it is returning an empty result for the following query.
$categories = \DB::table('floor_plans as fp')
->join('unit_type_details as utd','fp.unit_type_detail_id','utd.id')
->join('amenity_pricing_reviews as apr', strtolower('fp.pms_unit_type'), strtolower('apr.pms_unit_type'))
->select('fp.id','fp.unit_type_detail_id', 'fp.pms_unit_type', 'fp.unit_count', 'fp.pricing_offset', 'apr.dom','utd.unit_type')
->where('fp.property_id',$property->id)
->whereNull('fp.deleted_at')
->whereNull('utd.deleted_at')
->whereNull('apr.deleted_at')
->orderBy('fp.pms_unit_type','asc')
->get()
->groupBy(['unit_type_detail_id','pms_unit_type']);
What could be the issue? From where I could start debugging it? I could confirm,
$property->id which is the only dynamic field on the query is also the same..
Start from the beginning,
->toSql() instead of ->get()) and start working with the raw query on your database.btw, why do you use strtolower('fp.pms_unit_type')? The string is already lowercase.
@click actually this fp.pms_unit_type is something I am doubting if it is the one that causing the issue, what I was actually trying is the compare the value in lower case, but after you point it. I think I was not doing it correctly what I was trying to do.
Now, I could confirm, that the issue is in >join('amenity_pricing_reviews as apr', strtolower('fp.pms_unit_type'), strtolower('apr.pms_unit_type')), how could I lower the value of those columns instead? I want to match by lowercasing those column values.
@Inquisitive the result of the strtolower() is just fp.pms_unit_type and apr.pms_unit_type
->join('amenity_pricing_reviews as apr', strtolower('fp.pms_unit_type'), strtolower('apr.pms_unit_type'))
is the same as
->join('amenity_pricing_reviews as apr','fp.pms_unit_type', 'apr.pms_unit_type')
The strtolower does not do anything with your actual values in your database. Why do you want to lower those values? Mysql is not case sensitive in normal circumstances.
As a first step check all of your logs.
@jlrdw actually, it is not throwing any error, its just returning empty collections, so there is nothing on logs.
@Inquisitive did you also look at MySQL logs?
Edit
Also you do have the data imported on live server, right?
Example values on that columns are 32115a2,''
By the way, it is apache on local server and ngnix on staging, could there be anything that might be playing role here?
Might be a difference in mysql configuration? Could the handling of null columns be different?
try leftjoin instead of join
check the collation used on each db
@Snapey I tried with the same collation in local and it works as well, but not in dev.
Surprisingly, it works with left join, and here is the output, but I don't see why it is not working with Inner Join
Do you have any idea, why it is not be working with Inner Join
Surprisingly, it works with left join,
Are you using "strict mode" in both config?
@sr57 how could i check it?
in config/database.php
but please double check directly in db
SHOW VARIABLES LIKE 'sql_mode';
@sr57 just checked, and its same on both.
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Ok it was the first check to do.
Difference in result (inner vs left join) should be caused by mysql optimizer that works differently for different version of mysql.
You should be able to see this by having a look to the explain outputs of the sql in both config.
Please or to participate in this conversation.