php - 使用 laravel 从多个表进行多个查询

Question

我想根据用户搜索选项从数据库中查询 12 个表。

这是表格的可视化模式图

selected tables to query visual schema diagram

这是用户将选择以搜索数据库的选择选项的 UI。

UI of query fields

这是我试过但总是失败的 Controller 。

$center = Input::get("center");
    $course = Input::get("course");
    $parentInfo = Input::get("parent-info");
    $feedingList = Input::get("feeding-list");
    $kitCollectionList = Input::get("kit-colection-list");
    $paymentStatus = Input::get("payment-status");
    $verifiedPayment = Input::get("verified-payments");
    $attendanceList = Input::get("attendance-list");
    $laptopRentalList = Input::get("laptop-rental-list");
    $pinList = Input::get("pins-list");
    $siblings = Input::get("siblings");
    $gender = Input::get("gender");
    $birthday = Input::get("birthdays");
    $schoolList = Input::get("school-list");
    $schoolLocation = Input::get("school-location");
    $studentAge = Input::get("student-age");
    $studentClass = Input::get("student-class");
    $stateOfOrigin = Input::get("state-of-origin");
    $chequeList = Input::get("cheque-list");



    $results = DB::table('enrollments')
             ->join('students', 'enrollments.id', '=', 'students.enrollment_id')
             ->join('parents', 'enrollments.id', '=', 'parents.enrollment_id')
             // ->join('student_pickups', 'enrollments.id', '=', 'student_pickups.enrollment_id')
             ->join('pins', 'enrollments.id', '=', 'pins.enrollment_id')
             ->join('student_proxies', 'enrollments.id', '=', 'student_proxies.enrollment_id')
             ->join('student_courses', 'enrollments.id', '=', 'student_courses.enrollment_id')
             ->join('student_feedings', 'enrollments.id', '=', 'student_feedings.enrollment_id')
             ->join('student_laptop_rentals', 'enrollments.id', '=', 'student_laptop_rentals.enrollment_id')
             ->join('student_expos', 'enrollments.id', '=', 'student_expos.enrollment_id')
             ->join('payments', 'enrollments.id', '=', 'payments.enrollment_id')
             ->join('payment_methods', 'enrollments.id', '=', 'payment_methods.enrollment_id')
             ->join('student_camp_materials', 'enrollments.id', '=', 'student_camp_materials.enrollment_id')
             ->join('attendances', 'enrollments.id', '=', 'attendances.enrollment_id')
             ->select('students.center',
                      'students.student_name',
                      'students.student_dob',
                      'students.student_sex',
                      'students.student_email',
                      'students.student_phone',
                      'students.student_home_address',
                      'students.student_city',
                      'students.student_school_name',
                      'students.student_school_city',
                      'students.student_school_state',
                      'students.student_state_of_origin',
                      'students.student_school_address',
                      'students.student_school_class',
                      'students.created_at',
                      'parents.parent_title',
                      'parents.parent_name',
                      'parents.relationship',
                      'parents.parent_email',
                      'parents.parent_phone',
                      'parents.parent_home_address',
                      'parents.parent_work_address',
                      'parents.parent_office_address',
                      'parents.created_at',
                      /*'student_pickups.child_self_pickup',
                      'student_pickups.drop_off_by_center4tech',
                      'student_pickups.parent_or_guardian_pick_up',
                      'student_pickups.proxy_pick_up',
                      'student_pickups.created_at',*/
                      'pins.pin_owner_name',
                      'pins.user_type',
                      'pins.status',
                      'pins.sign_in_pin',
                      'pins.sign_out_pin',
                      'pins.pick_up_pin',
                      'pins.drop_off_pin',
                      'pins.created_at',
                      'student_proxies.proxy_title',
                      'student_proxies.proxy_name',
                      'student_proxies.proxy_gender',
                      'student_proxies.proxy_phone',
                      'student_proxies.proxy_relationship',
                      'student_proxies.created_at',
                      'student_courses.course_name',
                      'student_courses.course_type',
                      'student_courses.course_price',
                      'student_courses.center',
                      'student_courses.computer_usage_proficiency',
                      'student_courses.created_at',
                      'student_feedings.feeding_pack_label',
                      'student_feedings.feeding_price',
                      'student_feedings.feeding_start_date',
                      'student_feedings.feeding_end_date',
                      'student_feedings.feeding_expire_notification_sms',
                      'student_feedings.feeding_expire_notification_email',
                      'student_feedings.feeding_expire_notification_parent_reminder',
                      'student_feedings.created_at',
                      'student_laptop_rentals.laptop_rental_label',
                      'student_laptop_rentals.laptop_rental_price',
                      'student_laptop_rentals.laptop_rental_start_date',
                      'student_laptop_rentals.laptop_rental_end_date',
                      'student_laptop_rentals.laptop_rental_expire_notification_sms',
                      'student_laptop_rentals.laptop_rental_expire_notification_email',
                      'student_laptop_rentals.laptop_rental_expire_notification_parent_reminder',
                      'student_laptop_rentals.is_rented',
                      'student_laptop_rentals.is_returned',
                      'student_laptop_rentals.created_at',
                      'student_expos.expo_fees',
                      'student_expos.expo_price',
                      'student_expos.created_at',
                      'payments.payment_type',
                      'payments.invoice_number',
                      'payments.item_description',
                      'payments.quantity',
                      'payments.rate',
                      'payments.discount',
                      'payments.total',
                      'payments.transaction_date',
                      'payments.verified',
                      'payments.invoice_sent',
                      'payments.invoice_sent_by_sms',
                      'payments.invoice_sent_by_email',
                      'payments.invoice_sent_date',
                      'payments.created_at',
                      'payment_methods.payment_option',
                      'payment_methods.payment_amount',
                      'payment_methods.cash_payment_confirmation',
                      'payment_methods.cash_transaction_timestamp',
                      'payment_methods.bank_pos_provider',
                      'payment_methods.bank_pos_transaction_date',
                      'payment_methods.bank_pos_transaction_time',
                      'payment_methods.bank_pos_stan',
                      'payment_methods.bank_pos_rrn',
                      'payment_methods.bank_pos_ref_no',
                      'payment_methods.bank_online_sender_account_name',
                      'payment_methods.bank_online_sender_account_number',
                      'payment_methods.bank_online_sender_bank',
                      'payment_methods.bank_online_transaction_date',
                      'payment_methods.bank_online_miu_research_bank_acc_paid_into',
                      'payment_methods.bank_online_payment_channel',
                      'payment_methods.cheque_name',
                      'payment_methods.cheque_date_to_pay_in',
                      'payment_methods.cheque_to_be_paid_in_now',
                      'payment_methods.cheque_is_post_dated',
                      'payment_methods.cheque_notification_reminder',
                      'payment_methods.bank_teller_depositor_name',
                      'payment_methods.bank_teller_transaction_date',
                      'payment_methods.bank_teller_miu_research_bank_acc_paid_into',
                      'payment_methods.is_splited_by_cash',
                      'payment_methods.split_pay_cash_amount',
                      'payment_methods.is_splited_by_pos',
                      'payment_methods.split_pay_pos_amount',
                      'payment_methods.is_splited_by_bank_transfer_online',
                      'payment_methods.split_pay_bank_transfer_pc_mobile_amount',
                      'payment_methods.is_splited_by_teller',
                      'payment_methods.split_pay_deposit_teller_amount',
                      'payment_methods.is_splited_by_cheque',
                      'payment_methods.split_pay_cheque_amount',
                      'payment_methods.is_parted_by_cash',
                      'payment_methods.is_parted_by_pos',
                      'payment_methods.is_parted_by_bank_transfer',
                      'payment_methods.is_parted_by_teller',
                      'payment_methods.is_parted_by_cheque',
                      'payment_methods.advance_payment_amount',
                      'payment_methods.balance_payment_outstanding',
                      'payment_methods.balance_payment_due_date',
                      'payment_methods.balance_payment_status',
                      'payment_methods.pdf_generated',
                      'payment_methods.receipt_sent',
                      'payment_methods.receipt_sent_by_sms',
                      'payment_methods.receipt_sent_by_email',
                      'payment_methods.receipt_sent_by_pdf',
                      'payment_methods.receipt_sent_date',
                      'payment_methods.status',
                      'payment_methods.created_at',
                      'student_camp_materials.course_material_given',
                      'student_camp_materials.electronic_kit_given',
                      'student_camp_materials.enrollment_completed',
                      'student_camp_materials.enrollment_not_completed',
                      'student_camp_materials.created_at',
                      'attendances.present',
                      'attendances.absent',
                      'attendances.sign_in',
                      'attendances.sign_out',
                      'attendances.operation',
                      'attendances.extra',
                      'attendances.created_at',
                     )
            // ->orWhere('students.center','LIKE','%'.$center.'%')
             ->orWhere('students.center',$center)
             ->orWhere('student_courses.course_name',$course)
             ->orWhere('parents.parent_phone')
             ->orWhere('parents.parent_email')
             ->orWhere('parents.parent_office_address')
             ->orWhere('student_courses.course_name',$course)
             ->get();


    /*if ( isset($center) ) {

        $results = Student::where("center",$center)->get();
    }

    if ( isset($course) ) {

        $results = Studentcourse::where("course_name",$course)->get();
    }*/

    return $results;

Answer 1

这肯定是一个很长的查询，我确信需要进行一些优化 - 代码和查询。

您需要更详细地说明您遇到的错误类型以及我们如何提供帮助，例如预期结果等。但是，我很快发现了一些事情 -

1. 那个 Controller 永远不会成功，查询有缺陷。

2. 这些变量中的大多数似乎都未被使用。您最好直接从请求中调用它们并保存几行代码。

3. 您的一些 where's 甚至没有条件 - 这会导致失败。

->orWhere('parents.parent_phone')
->orWhere('parents.parent_email')
->orWhere('parents.parent_office_address')

1. 可能值得对您的查询进行更多分组。例如

->where(function ($builder) {
    $builder
        ->where('students.center', request()->center)
        ->orWhere('student_courses.course_name', $course = request()->course)
        ->orWhere('student_courses.course_name', $course);
})
->get();

总的来说，我真的建议检查您的所有代码并在可能的情况下对其进行整理。