Search Query Laravel Input

Question

I have a big search query and all works great but I need to show the Packages(seminars) for each user to this blade

 <tbody>
        @foreach($users as $i=>$user)
            <tr>
                <td>{{ $i+1 }}</td>
                <td>{{ $user->home_lastname }}</td>
                <td>{{ $user->home_firstname }} </td>
      //------> <td> HERE I NEED TO SHOW ALL THE PACKAGES NAMES(SEMINARS) FOR EACH USER</td>
                <td>{{ $user->home_id }}</td>
                <td></td>

UserController

 $builder = User::query();

    if (Input::has('lastname')) {
        $queryString = Input::get('lastname');
        $builder->where('home_lastname', 'LIKE', "%$queryString%");
    }

    if (Input::has('name')) {
        $queryString = Input::get('name');
        $builder->where('home_firstname', 'LIKE', "%$queryString%");
    }

    if (Input::has('id')) {
        $queryString = Input::get('id');
        $builder->where('home_id', 'LIKE', "%$queryString%");
    }

    if (Input::has('type-select')){
        $userType = Input::get('type-select');
        $builder->select('*')
            ->where('Home_Students.home_elv_rights',$userType)
            ->get();
    }


    if(Input::has('seminar-select')){
        $seminar = Input::get('seminar-select');
        $builder->select('*')
            ->join('Home_StudentPackages','Home_StudentPackages.hsp_homeStudID','=','Home_Students.home_id')
            ->join('Home_Packages','Home_Packages.Package_ID','=','Home_StudentPackages.hsp_packid')
            ->where('Home_Packages.Package_ID',$seminar)
            ->get();
    }

    if(Input::has('class-select')){
        $class = Input::get('class-select');
        $builder->select('*')
            ->join('Hw_StudentClasses','Hw_StudentClasses.Stclass_studentid','=','Home_Students.home_id')
            ->join('Hw_Classes','Hw_Classes.Class_id','=','Hw_StudentClasses.Stclass_classid')
            ->where('Hw_Classes.Class_id',$class)
            ->get();
    }

    if(Input::has('date_created_from')) {
        $date = date("Y-m-d", strtotime(Input::get('date_created_from')));
        $builder->select('*')
            ->whereBetween('Home_Students.home_dateCreated', [$date, date("Y-m-d")])
            ->get();
        }
        elseif(Input::has('date_created_from','date_created_untill')){
            $date = date("Y-m-d", strtotime(Input::get('date_created_from')));
            $date2 = date("Y-m-d", strtotime(Input::get('date_created_untill')));
            $builder->select('*')
                ->whereBetween('Home_Students.home_dateCreated', [$date,$date2])
                ->get();
            }
            elseif(Input::has('date_created_untill')){
            $date2 = date("Y-m-d", strtotime(Input::get('date_created_untill')));
            $date = date("Y-m-d", strtotime(Input::get('date_created_from')));
                $builder->select('*')
                ->whereBetween('Home_Students.home_dateCreated',[$date,$date2])
                ->get();
            }




    // ... more clauses from the querystring
    $users = $builder->orderBy('home_lastname')->get();



        return View::make('user.show')->with(array('users' => $users));
    }

I tried these joins to the start $builder query and to the builder in the end of the controller but it didn't work. Also the wheres in each if didn't work and so I don't know any other way to do this.

DB::table('Home_Students')
            ->select('home_lastname','home_firstname','home_id','Package_Name')
            ->join('Home_StudentPackages','Home_StudentPackages.hsp_homeStudID','=','Home_Students.home_id')
            ->join('Home_Packages','Home_Packages.Package_ID','=','Home_StudentPackages.hsp_PackID'
            ->get();

I need just to show Packages_Names for each User to my blade View but there is a problem with the search by Seminar (Packages).

EDIT

I am trying to create public function in the model of the user where I will get all packages (seminars) for each User; do you know any way to do this?

There is a pivot table that combines users and packages!

EDIT 2 I think this is wrong because it returns [{"Package_Name":"Excel"}] - is it possible to return this as a string ?

 public function getUserSeminars(){



        $users = Seminar::select('Package_Name')
            ->join('Home_StudentPackages','Home_StudentPackages.hsp_PackID','=','Home_Packages.Package_ID')
            ->join('Home_Students','Home_Students.home_id','=','Home_StudentPackages.hsp_homeStudID')
            ->where('Home_Students.home_id','=',$this->home_id)
            ->get();

        return $users;
    }

JP.dev · Accepted Answer · 2016-04-06 22:27:17Z

1

Not to do it so complicated the search query and inside the controller i went to my User model and i did this function:

public function getUserSeminars(){


        $seminar = Seminar::select('Package_Name')
            ->join('Home_StudentPackages','Home_StudentPackages.hsp_PackID','=','Home_Packages.Package_ID')
            ->join('Home_Students','Home_Students.home_id','=','Home_StudentPackages.hsp_homeStudID')
            ->where('Home_Students.home_id','=',$this->home_id)
            ->get();

        return $seminar;



    }

Then i called it in the blade like this

                <td>
                    @foreach($user->getUserSeminars() as $seminar)
                        <li>    {{$seminar->Package_Name}}  </li>
                    @endforeach
                </td>

It worked like a charm. Hope this helps someone too!

edited Apr 6, 2016 at 22:27

answered Apr 5, 2016 at 15:54

JP.dev

1701 silver badge18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

veksen Over a year ago

Realize that you're running n+1 queries with this approach.

JP.dev Over a year ago

I couldnt do it with another approach cause i have an if that does the same query so it craches any other way and another way i tried it gives me result but it gives me 3 or 4 times the user for each seminar and i cant make disctinct or something else do you have any other suggestions ?

Collectives™ on Stack Overflow

Search Query Laravel Input

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related