Level 6
Sometimes we see a clear difference between donors, but I don't know why?
Jan 26, 2023
32
Level 6
filter search
Hello / My biggest problem is creating a search filter. How to leave several inputs blank in the search form and fill one input and perform the search. I thought about this way for several days, but I could not solve it.
search form
<form action="{{ route('rentsearch') }}" method="GET">
@csrf
<input type="text" name="street"><br>
<input type="number" name="pish"><br>
<input type="number" name="mah"><br>
<input type="number" name="metraj"><br>
<input type="number" name="bedroom"><br>
<input type="checkbox" value="1" name="parking" id="parking">
<input type="checkbox" value="1" name="elv" id="elv">
<input type="checkbox" value="1" name="storeroom" id="storeroom">
<button type="submit" class="btn btn-primary col-10">search</button>
</form>
controller
$operators = [
['parking', '=', $request->boolean('parking')],
['elv', '=', $request->boolean('elv')],
['storeroom', '=', $request->boolean('storeroom')],
// ];
// if (!empty($request->street)) {
$operators[] = ['street', 'LIKE', "%{$request->street}%" ],
// }
// if (!empty($request->pish)) {
$operators[] = ['pish', '<=', $request->pish],
// }
// if (!empty($request->mah)) {
$operators[] = ['mah', '<=', $request->mah],
// }
// if (!empty($request->bedroom)) {
$operators[] = ['bedroom', '<=', $request->bedroom],
// }
// if (!empty($request->metraj)) {
$operators[] = ['metraj', '<=', $request->metraj],
];
dd(Rent::where($operators)->toSql(), $request->all());
$dat = Rent::where($operators)->get();
return view(
dd($dat); and fill input street
"select * from `rents` where (`parking` = ? and `elv` = ? and `storeroom` = ? and `street` LIKE ? and `pish` <= ? and `mah` <= ? and `bedroom` <= ? and `metra ▶" // app\Http\Controllers\HouseController.php:78
array:9 [▼ // app\Http\Controllers\HouseController.php:78
"_token" => "bqhen1GAlmR2G5ub4hsu52lpAjxxE1D5K4NSlzzU"
"street" => "love"
"pish" => "10000000000"
"mah" => "10000000000"
"metraj" => "10000000000"
"bedroom" => "10000000000"
"parking" => "on"
"elv" => "on"
"storeroom" => "on"
]
Level 102
This is how I would do it. All except the booleans are checked for if they are present
$dat = Rent::query()
->where('parking', $request->boolean('parking'))
->where('elv', $request->boolean('elv'))
->where('storeroom', $request->boolean('storeroom'))
->when($request->input('street'), fn($query, $value) => $query->where('street', 'LIKE', "%{$value}%"))
->when($request->input('pish'), fn($query, $value) => $query->where('pish', '<=', $value))
->when($request->input('mah'), fn($query, $value) => $query->where('mah', '<=', $value))
->when($request->input('bedroom'), fn($query, $value) => $query->where('bedroom', '<=', $value))
->when($request->input('metraj'), fn($query, $value) => $query->where('metraj', '<=', $value))
->get();
Level 102
Donors ?
Level 35
You could try using the eloquent when method for applying a conditional where statement if a particular input is filled.
->when($request->filled('street'), fn($q) => $q->where('street', 'LIKE', "%{$request->street}%"))
Level 102
If you want to use filled(). Just asked ChatGPT to change it :p
$dat = Rent::query()
->where('parking', $request->boolean('parking'))
->where('elv', $request->boolean('elv'))
->where('storeroom', $request->boolean('storeroom'))
->when($request->input('street'), fn($query) => $query->where('street', 'LIKE', "%{$request->input('street')}%"))
->when($request->input('pish'), fn($query) => $query->where('pish', '<=', $request->input('pish')))
->when($request->input('mah'), fn($query) => $query->where('mah', '<=', $request->input('mah')))
->when($request->input('bedroom'), fn($query) => $query->where('bedroom', '<=', $request->input('bedroom')))
->when($request->input('metraj'), fn($query) => $query->where('metraj', '<=', $request->input('metraj')))
->get();
Level 6
@Sinnbeck dd($dat);
Illuminate\Database\Eloquent\Collection {#1816 ▼ // app\Http\Controllers\HouseController.php:97
#items: []
#escapeWhenCastingToString: false
}
Level 102
@LoverToHelp and ->dd() ? Just replace ->get() with ->dd()
Level 6
@Sinnbeck its your mean?
$dat = Rent::query()
->where('parking', $request->boolean('parking'))
->where('elv', $request->boolean('elv'))
->where('storeroom', $request->boolean('storeroom'))
->when($request->input('street'), fn($query) => $query->where('street', 'LIKE', "%{$request->input('street')}%"))
->when($request->input('pish'), fn($query) => $query->where('pish', '<=', $request->input('pish')))
->when($request->input('mah'), fn($query) => $query->where('mah', '<=', $request->input('mah')))
->when($request->input('bedroom'), fn($query) => $query->where('bedroom', '<=', $request->input('bedroom')))
->when($request->input('metraj'), fn($query) => $query->where('metraj', '<=', $request->input('metraj')))
->dd();
it show
"select * from `rents` where `parking` = ? and `elv` = ? and `storeroom` = ? and `street` LIKE ?" // vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:3754
array:4 [▼ // vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:3754
0 => false
1 => false
2 => false
3 => "%love%"
]
Level 102
@LoverToHelp Yes. So do you have any records where all those 3 are false ?
Level 6
@Sinnbeck @Sinnbeck its your mean?
$dat = Rent::query()
->where('parking', $request->boolean('parking'))
->where('elv', $request->boolean('elv'))
->where('storeroom', $request->boolean('storeroom'))
->when($request->input('street'), fn($query) => $query->where('street', 'LIKE', "%{$request->input('street')}%"))
->when($request->input('pish'), fn($query) => $query->where('pish', '<=', $request->input('pish')))
->when($request->input('mah'), fn($query) => $query->where('mah', '<=', $request->input('mah')))
->when($request->input('bedroom'), fn($query) => $query->where('bedroom', '<=', $request->input('bedroom')))
->when($request->input('metraj'), fn($query) => $query->where('metraj', '<=', $request->input('metraj')))
->dd();
it show
"select * from `rents` where `parking` = ? and `elv` = ? and `storeroom` = ? and `street` LIKE ?" // vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:3754
array:4 [▼ // vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:3754
0 => false
1 => false
2 => false
3 => "%love%"
]
Level 102
@LoverToHelp No I am asking you to check your database. It seems that you have no records (0 records) that has parking = false AND storeroom = false AND elv = false
Level 6
@Sinnbeck I have two data or building , one has elevator and parking and one has only elevator
Level 102
@LoverToHelp So then it makes sense that you get zero records returned. Is it because its meant to be optional? As in the checkboxes just mean "it has to have parking" ? I can see how that makes sense :)
Try this instead then
$dat = Rent::query()
->when($request->boolean('parking'), fn($query, $value) => $query->where('parking', $value))
->when($request->boolean('elv'), fn($query, $value) => $query->where('elv', $value))
->when($request->boolean('storeroom'), fn($query, $value) => $query->where('storeroom', $value))
->when($request->input('street'), fn($query, $value) => $query->where('street', 'LIKE', "%{$value}%"))
->when($request->input('pish'), fn($query, $value) => $query->where('pish', '<=', $value))
->when($request->input('mah'), fn($query, $value) => $query->where('mah', '<=', $value))
->when($request->input('bedroom'), fn($query, $value) => $query->where('bedroom', '<=', $value))
->when($request->input('metraj'), fn($query, $value) => $query->where('metraj', '<=', $value))
->get();
Level 35
@Sinnbeck I wasn't responding to your comment. When I started my comment, your comments weren't there. It just took me awhile because I was typing on my phone and then got interrupted by someone coming to the door.
Level 102
@Ben Taylor Yeah it was for @lovertohelp if he wanted to use that syntax :) I know the horrors of typing code on my phone.
1 like
Level 6
@Sinnbeck You know choosing a house for rent. A person who does not care about the price does not enter a number and the field remains empty. Maybe only the elevator is important to him, and in the search, he finds items with an elevator, so the rest of the fields remain empty.
Level 102
@LoverToHelp Yeah then that last code should work :)
Level 6
engineer @Sinnbeck showed my main problem now. I am also pulling information from two other tables and they have a one-to-many relationship. The error is now:
SQLSTATE[42S22]: Column not found: 1054 Unknown column '[{"id"?,"user_id"?,"project_id"?,"house_id"?,"title":"\u06a9\u0631\u0645\u0627\u0646","pish"?,"mah"?,"desc":"<p>\u0633\u0631\u0648\u06cc\u0633 \u0628\u0647\u062f\u0627\u0634\u062a\u06cc\u0633\' in 'where clause'
my databse
Schema::create('rents', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('user_id');
$table->unsignedBigInteger('project_id');
$table->unsignedBigInteger('house_id');
$table->text('title');
$table->bigInteger('pish');
$table->bigInteger('mah');
$table->text('desc');
$table->text('type');
$table->text('floor');
$table->integer('metraj');
$table->integer('bedroom');
$table->text('wc');
$table->text('kaf');
$table->text('cabinet');
$table->text('cooler');
$table->string('thumbnail');
$table->boolean('parking')->default('0');
$table->boolean('elv')->default('0');
$table->boolean('storeroom')->default('0');
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->foreign('project_id')->references('id')->on('projects')->onDelete('cascade');
$table->foreign('house_id')->references('id')->on('houses')->onDelete('cascade');
$table->timestamps();
Level 102
@LoverToHelp What code throws that error? The code I provided?
Level 6
yes @Sinnbeck
Level 102
@LoverToHelp It didnt give that error before? You just got no records. Can you show the output of
$dat = Rent::query()
->when($request->boolean('parking'), fn($query, $value) => $query->where('parking', $value))
->when($request->boolean('elv'), fn($query, $value) => $query->where('elv', $value))
->when($request->boolean('storeroom'), fn($query, $value) => $query->where('storeroom', $value))
->when($request->input('street'), fn($query, $value) => $query->where('street', 'LIKE', "%{$value}%"))
->when($request->input('pish'), fn($query, $value) => $query->where('pish', '<=', $value))
->when($request->input('mah'), fn($query, $value) => $query->where('mah', '<=', $value))
->when($request->input('bedroom'), fn($query, $value) => $query->where('bedroom', '<=', $value))
->when($request->input('metraj'), fn($query, $value) => $query->where('metraj', '<=', $value))
->dd();
Level 6
@Sinnbeck we filled form https://s2.uupload.ir/files/se_b3wh.png dd
"select * from `rents` where `pish` <= ?" // vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:3754
array:1 [▼ // vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:3754
0 => "1000"
]
Level 102
@LoverToHelp That query tooks nothing like the error? I dont understand where that error comes from? Can you perhaps share the error page (there is a button)
Level 6
@Sinnbeck this is your means :
Level 102
@LoverToHelp no the error page. Go back to ->get() and I assume you get the error again?
Level 6
Excuse me, dear engineer @Sinnbeck , I will create a new question and write more fully https://laracasts.com/discuss/channels/laravel/search-form-1
Level 102
@LoverToHelp Why? Lets get this solved. We should be getting very close. I dont see the idea of starting completely over from scratch
Level 6
Hello @Sinnbeck This translation is Google Translate. no fault I was just upset. All questions are visible except my question
Level 102
@LoverToHelp I could see your question? And answered it. Just could not do so from my phone, so I had to wait to get to my computer. Also I dont work for laracasts, so I dont care if you are a paying member or not.
Level 6
@Sinnbeck If you and some other friends are not on the site, the site should be closed
Level 6
@Sinnbeck YouTube and many sites are filtered in our country, otherwise I would be one of the best like you.
Level 102
@LoverToHelp Sorry to hear that :( That must suck. VPN not an option ? Or is that illigal?
Please or to participate in this conversation.