3

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;
    }
halfer
  • 19,824
  • 17
  • 99
  • 186
JP.dev
  • 170
  • 1
  • 18

1 Answers1

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!

JP.dev
  • 170
  • 1
  • 18
  • Realize that you're running n+1 queries with this approach. – veksen Apr 05 '16 at 17:21
  • 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 ? – JP.dev Apr 05 '16 at 17:30