Hello guys, my following query is running well. But I am finding it quite difficult to use it as query builder. I am just willing to make equivalent query builder of following:
/*DONATION RANGE*/
$donation_range = '1 = 1';
if($request->donation_range_checkbox){
$donation_range = "(D.donation_amount >= '".$request->min_donation_amount."' AND D.donation_amount <= '".$request->max_donation_amount."')";
}else{
if(trim($request->donation_amount) != ''){
$donation_range = "D.donation_amount = '".$request->donation_amount."'";
}
}
/*DURATION*/
$duration = '1 = 1';
if($request->period != 'all' ){
if($request->period == 'custom'){
$start_date = $request->datestart;
$end_date = $request->dateend;
$start_date = date('Y-m-d', strtotime($request->datestart) );
$end_date = date('Y-m-d', strtotime($request->dateend) );
$duration = "D.donation_date between '".$start_date."' AND '".$end_date."' ";
}else{
$duration = "D.donation_date > DATE_SUB('".date('Y-m-d')."', INTERVAL '".$request->duration."' DAY)";
}
}
/*STATE FILTER*/
$state_filter = '1 = 1';
$state_arr = array_map( 'trim', explode( ",",$request->state ) );
$state_arr_str = implode("','",$state_arr);
if(trim($state_arr_str) != '')
{
$state_filter = "B.home_address_state IN ('".$state_arr_str."') OR B.mailing_address_state IN ('".$state_arr_str."')";
}
/*CITY FILTER*/
$city_filter = '1 = 1';
$city_arr = array_map( 'trim', explode( ",",$request->city ) );
$city_arr_str = implode("','",$city_arr);
if(trim($city_arr_str) != '')
{
$city_filter = "B.home_address_city IN ('".$city_arr_str."') OR B.mailing_address_city IN ('".$city_arr_str."')";
}
/*STREET FILTER*/
$street_filter = '1 = 1';
$street_arr = array_map( 'trim', explode( ",",$request->street ) );
$street_arr_str = implode("','",$street_arr);
if(trim($street_arr_str) != '')
{
$street_filter = "B.home_street_address_1 IN ('".$street_arr_str."') OR B.home_street_address_2 IN ('".$street_arr_str."') OR B.home_street_address_3 IN ('".$street_arr_str."') OR B.mailing_street_address_1 IN ('".$street_arr_str."') OR B.mailing_street_address_2 IN ('".$street_arr_str."') OR B.mailing_street_address_3 IN ('".$street_arr_str."')";
}
/*HOME DIRECTION*/
$home_direction_filter = '1=1';
if(trim($request->direction) != '')
{
$home_direction_filter = "C.home_direction = '".$request->direction."'";
}
/*HOUSE NUMBER*/
$house_number = '1 = 1';
if($request->house_number_checkbox){
$house_number = "(C.home_house_number >= '".$request->min_house_number."' AND C.home_house_number <= '".$request->max_house_number."')";
}else{
if(trim($request->house_number) != ''){
$house_number = "C.home_house_number = '".$request->house_number."'";
}
}
/*ZIP CODE FILTER*/
$zip_code_filter = '1 = 1';
$zip_code_arr = array_map( 'trim', explode( ",",$request->zip_code ) );
$zip_code_arr_str = implode("','",$zip_code_arr);
if(trim($zip_code_arr_str) != '')
{
$zip_code_filter = "B.home_address_zip IN ('".$zip_code_arr_str."') OR B.home_address_zip_plus IN ('".$zip_code_arr_str."') OR B.mailing_address_zip IN ('".$zip_code_arr_str."') OR B.mailing_address_zip_plus IN ('".$zip_code_arr_str."')";
}
/*GENDER*/
$gender_filter = '1=1';
if(trim($request->gender) != '')
{
$gender_filter = "B.gender LIKE '".$request->gender."%'";
}
/*AGE RANGE*/
$age = '1 = 1';
if($request->age_checkbox){
if($request->min_age){
$max_birth_year = date('Y') - $request->min_age; //year will be max as opposed to age
}else{
$max_birth_year = date('Y');
}
if($request->max_age){
$min_birth_year = date('Y') - $request->max_age; //year will be min as opposed to age
}else{
$min_birth_year = 0;
}
$age = "(B.birth_year >= '".$min_birth_year."' AND B.birth_year <= '".$max_birth_year."')";
}else{
if($request->age){
$birth_year = date('Y') - $request->age;
$age = "B.birth_year = '".$birth_year."'";
}
}
/*VOTER PARTY*/
$party_filter = '1=1';
if(trim($request->party) != '')
{
$party_filter = "B.voter_party LIKE '".$request->party."%'";
}
/*TAGS*/
$tags_filter = '1=1';
if(trim($request->tags) != '')
{
$tags_filter = "N.note_types LIKE '%".$request->tags."%'";
}
/*BASE_VOTER_MY_LIST*/
$my_list_filter = "B.id NOT IN (SELECT M.base_voter_id from base_voter_my_list M where M.my_list_id = '".$my_list_id."')";
$sqlx = "SELECT DISTINCT B.* from base_voter B LEFT OUTER JOIN
contribution_data R ON B.contribution_data_table_id = R.id LEFT OUTER JOIN
contribution_donation D ON R.id = D.contribution_data_id LEFT OUTER JOIN
city_voter C ON B.city_voter_table_id = C.id LEFT OUTER JOIN
notes N ON B.id = N.base_voter_id LEFT OUTER JOIN
base_voter_my_list M ON B.id = M.base_voter_id
WHERE (".$donation_range.") AND (".$state_filter.") AND (".$city_filter.") AND (".$street_filter.") AND (".$home_direction_filter.") AND (".$house_number.") AND (".$zip_code_filter.") AND (".$gender_filter.") AND (".$age.") AND (".$party_filter.") AND (".$tags_filter.") AND (".$my_list_filter.")
";
So, far I have tried as follows:
$base_voter_my_list = DB::table('base_voter_my_list')->select('*')->where('my_list_id',$my_list_id)->pluck('base_voter_id')->toArray();
$results = DB::table('base_voters')
->leftJoin('contribution_data', 'base_voters.contribution_data_table_id', '=', 'contribution_data.id')
->leftJoin('contribution_donation', 'contribution_data.id', '=', 'contribution_donation.contribution_data_id')
->leftJoin('city_voter', 'base_voters.city_voter_table_id', '=', 'city_voter.id')
->leftJoin('notes', 'base_voters.id', '=', 'notes.base_voter_id')
->leftJoin('base_voter_my_list', 'base_voters.id', '=', 'base_voter_my_list.base_voter_id')
->where(function($query) use ($request) {
/*DONATION AMOUNT*/
if($request->donation_range_checkbox){
$query->where('contribution_donation.donation_amount','>=',$request->min_donation_amount)
->where('contribution_donation.donation_amount','<=',$request->min_donation_amount);
// $query->whereBetween('contribution_donation.donation_amount', [$request->min_donation_amount, $request->min_donation_amount]);
}else{
if(trim($request->donation_amount) != ''){
$query->where('contribution_donation.donation_amount','=',$request->donation_amount);
}
}
/*DURATION*/
if($request->period != 'all' ){
if($request->period == 'custom'){
$start_date = $request->datestart;
$end_date = $request->dateend;
$start_date = date('Y-m-d', strtotime($request->datestart) );
$end_date = date('Y-m-d', strtotime($request->dateend) );
$query->whereBetween('contribution_donation.donation_date', [$start_date, $end_date]);
}else{
$query->where('contribution_donation.donation_date','>',DB::raw(''.date('Y-m-d').', INTERVAL "'.$request->duration.'" DAY)'));
}
}
/*STATE FILTER*/
$state_arr = array_map( 'trim', explode( ",",$request->state ) );
$state_arr_str = implode("','",$state_arr);
if(trim($state_arr_str) != '')
{
$query->whereIn('base_voters.home_address_state',$state_arr_str)
->orWhereIn('base_voters.mailing_address_state',$state_arr_str);
}
/*CITY FILTER*/
$city_arr = array_map( 'trim', explode( ",",$request->city ) );
$city_arr_str = implode("','",$city_arr);
if(trim($city_arr_str) != '')
{
$query->whereIn('base_voters.home_address_city',$city_arr_str)
->orWhereIn('base_voters.mailing_address_city',$city_arr_str);
}
/*STREET FILTER*/
$street_arr = array_map( 'trim', explode( ",",$request->street ) );
$street_arr_str = implode("','",$street_arr);
if(trim($street_arr_str) != '')
{
$query->whereIn('base_voters.home_street_address_1',$street_arr_str)
->orWhereIn('base_voters.home_street_address_2',$street_arr_str)
->orWhereIn('base_voters.home_street_address_3',$street_arr_str)
->orWhereIn('base_voters.mailing_street_address_1',$street_arr_str)
->orWhereIn('base_voters.mailing_street_address_2',$street_arr_str)
->orWhereIn('base_voters.mailing_street_address_3',$street_arr_str);
}
/*HOME DIRECTION*/
if(trim($request->direction) != '')
{
$query->where('city_voter.home_direction','=',$request->direction);
}
/*HOUSE NUMBER*/
if($request->house_number_checkbox){
$query->where('city_voter.home_house_number','>=',$request->min_house_number)
->where('city_voter.home_house_number','<=',$request->max_house_number);
// $query->whereBetween('city_voter.home_house_number', [$request->min_house_number, $request->max_house_number]);
}else{
if(trim($request->house_number) != ''){
$query->where('city_voter.home_house_number','=',$request->house_number);
}
}
/*ZIP CODE FILTER*/
$zip_code_arr = array_map( 'trim', explode( ",",$request->zip_code ) );
$zip_code_arr_str = implode("','",$zip_code_arr);
if(trim($zip_code_arr_str) != '')
{
$query->whereIn('base_voters.home_address_zip',$zip_code_arr_str)
->orWhereIn('base_voters.home_address_zip_plus',$zip_code_arr_str)
->orWhereIn('base_voters.mailing_address_zip',$zip_code_arr_str)
->orWhereIn('base_voters.mailing_address_zip_plus',$zip_code_arr_str);
}
/*GENDER*/
if(trim($request->gender) != '')
{
$query->where('base_voters.gender','like',$request->gender.'%');
}
/*AGE RANGE*/
if($request->age_checkbox){
if($request->min_age){
$max_birth_year = date('Y') - $request->min_age; //year will be max as opposed to age
}else{
$max_birth_year = date('Y');
}
if($request->max_age){
$min_birth_year = date('Y') - $request->max_age; //year will be min as opposed to age
}else{
$min_birth_year = 0;
}
$age = "(B.birth_year >= '".$min_birth_year."' AND B.birth_year <= '".$max_birth_year."')";
$query->where('base_voters.birth_year','>=',$min_birth_year)
->where('base_voters.birth_year','<=',$max_birth_year);
// $query->whereBetween('base_voters.birth_year', [$min_birth_year, $max_birth_year]);
}else{
if($request->age){
$birth_year = date('Y') - $request->age;
$query->where('base_voters.birth_year','=',$birth_year);
}
}
/*VOTER PARTY*/
if(trim($request->party) != '')
{
$query->where('base_voters.voter_party','like',$request->party.'%');
}
/*TAGS*/
if(trim($request->tags) != '')
{
$query->where('base_voters.note_types','like','%'.$request->tags.'%');
}
$query->whereNotIn('base_voters.id',base_voter_my_list);
})
->get();$base_voter_my_list = DB::table('base_voter_my_list')->select('*')->where('my_list_id',$my_list_id)->pluck('base_voter_id')->toArray();
$results = DB::table('base_voters')
->leftJoin('contribution_data', 'base_voters.contribution_data_table_id', '=', 'contribution_data.id')
->leftJoin('contribution_donation', 'contribution_data.id', '=', 'contribution_donation.contribution_data_id')
->leftJoin('city_voter', 'base_voters.city_voter_table_id', '=', 'city_voter.id')
->leftJoin('notes', 'base_voters.id', '=', 'notes.base_voter_id')
->leftJoin('base_voter_my_list', 'base_voters.id', '=', 'base_voter_my_list.base_voter_id')
->where(function($query) use ($request) {
/*DONATION AMOUNT*/
if($request->donation_range_checkbox){
$query->where('contribution_donation.donation_amount','>=',$request->min_donation_amount)
->where('contribution_donation.donation_amount','<=',$request->min_donation_amount);
// $query->whereBetween('contribution_donation.donation_amount', [$request->min_donation_amount, $request->min_donation_amount]);
}else{
if(trim($request->donation_amount) != ''){
$query->where('contribution_donation.donation_amount','=',$request->donation_amount);
}
}
/*DURATION*/
if($request->period != 'all' ){
if($request->period == 'custom'){
$start_date = $request->datestart;
$end_date = $request->dateend;
$start_date = date('Y-m-d', strtotime($request->datestart) );
$end_date = date('Y-m-d', strtotime($request->dateend) );
$query->whereBetween('contribution_donation.donation_date', [$start_date, $end_date]);
}else{
$query->where('contribution_donation.donation_date','>',DB::raw(''.date('Y-m-d').', INTERVAL "'.$request->duration.'" DAY)'));
}
}
/*STATE FILTER*/
$state_arr = array_map( 'trim', explode( ",",$request->state ) );
$state_arr_str = implode("','",$state_arr);
if(trim($state_arr_str) != '')
{
$query->whereIn('base_voters.home_address_state',$state_arr_str)
->orWhereIn('base_voters.mailing_address_state',$state_arr_str);
}
/*CITY FILTER*/
$city_arr = array_map( 'trim', explode( ",",$request->city ) );
$city_arr_str = implode("','",$city_arr);
if(trim($city_arr_str) != '')
{
$query->whereIn('base_voters.home_address_city',$city_arr_str)
->orWhereIn('base_voters.mailing_address_city',$city_arr_str);
}
/*STREET FILTER*/
$street_arr = array_map( 'trim', explode( ",",$request->street ) );
$street_arr_str = implode("','",$street_arr);
if(trim($street_arr_str) != '')
{
$query->whereIn('base_voters.home_street_address_1',$street_arr_str)
->orWhereIn('base_voters.home_street_address_2',$street_arr_str)
->orWhereIn('base_voters.home_street_address_3',$street_arr_str)
->orWhereIn('base_voters.mailing_street_address_1',$street_arr_str)
->orWhereIn('base_voters.mailing_street_address_2',$street_arr_str)
->orWhereIn('base_voters.mailing_street_address_3',$street_arr_str);
}
/*HOME DIRECTION*/
if(trim($request->direction) != '')
{
$query->where('city_voter.home_direction','=',$request->direction);
}
/*HOUSE NUMBER*/
if($request->house_number_checkbox){
$query->where('city_voter.home_house_number','>=',$request->min_house_number)
->where('city_voter.home_house_number','<=',$request->max_house_number);
// $query->whereBetween('city_voter.home_house_number', [$request->min_house_number, $request->max_house_number]);
}else{
if(trim($request->house_number) != ''){
$query->where('city_voter.home_house_number','=',$request->house_number);
}
}
/*ZIP CODE FILTER*/
$zip_code_arr = array_map( 'trim', explode( ",",$request->zip_code ) );
$zip_code_arr_str = implode("','",$zip_code_arr);
if(trim($zip_code_arr_str) != '')
{
$query->whereIn('base_voters.home_address_zip',$zip_code_arr_str)
->orWhereIn('base_voters.home_address_zip_plus',$zip_code_arr_str)
->orWhereIn('base_voters.mailing_address_zip',$zip_code_arr_str)
->orWhereIn('base_voters.mailing_address_zip_plus',$zip_code_arr_str);
}
/*GENDER*/
if(trim($request->gender) != '')
{
$query->where('base_voters.gender','like',$request->gender.'%');
}
/*AGE RANGE*/
if($request->age_checkbox){
if($request->min_age){
$max_birth_year = date('Y') - $request->min_age; //year will be max as opposed to age
}else{
$max_birth_year = date('Y');
}
if($request->max_age){
$min_birth_year = date('Y') - $request->max_age; //year will be min as opposed to age
}else{
$min_birth_year = 0;
}
$age = "(B.birth_year >= '".$min_birth_year."' AND B.birth_year <= '".$max_birth_year."')";
$query->where('base_voters.birth_year','>=',$min_birth_year)
->where('base_voters.birth_year','<=',$max_birth_year);
// $query->whereBetween('base_voters.birth_year', [$min_birth_year, $max_birth_year]);
}else{
if($request->age){
$birth_year = date('Y') - $request->age;
$query->where('base_voters.birth_year','=',$birth_year);
}
}
/*VOTER PARTY*/
if(trim($request->party) != '')
{
$query->where('base_voters.voter_party','like',$request->party.'%');
}
/*TAGS*/
if(trim($request->tags) != '')
{
$query->where('base_voters.note_types','like','%'.$request->tags.'%');
}
$query->whereNotIn('base_voters.id',base_voter_my_list);
})
->get();
But it returning error: Invalid argument supplied for foreach().
