1

Any approach on how to use foreign keys when they are inside JSON?

Trying with the query builder but it only returns one task and inside the position model.

 $positions = DB::table('positions')
     ->join('tasks', function ($join) {
         $join->on('positions.id', '=', 'tasks.data->to_position->id');
     })
     ->get();

{ 
    "id": 14, 
    "code": "002", 
    "level": 1, 
    "side": "right", 
    "street_id": 1, 
    "position_type_id": 1, 
    "position_state_id": 2, 
    "granel": 0, 
    "kind": "picking", 
    "created_at": "2017-07-11 15:37:11", 
    "updated_at": "2017-07-14 16:36:38", 
    "task_state_id": 3, 
    "task_type_id": 2, 
    "data": "{\"orders\": [48], \"user_id\": 1, \"from_dock\": \"Muelle de ingreso pañol\", \"date_stored\": {\"date\": \"2017-07-14 16:36:38.000000\", \"timezone\": \"America/Argentina/Buenos_Aires\", \"timezone_type\": 3}, \"to_position\": {\"id\": 9, \"code\": \"002\", \"kind\": \"picking\", \"side\": \"right\", \"level\": 1, \"pivot\": {\"position_id\": 9, \"packaging_id\": 92, \"packaging_max\": 150, \"packaging_units\": 0, \"single_product_units\": 0}, \"state\": {\"id\": 3, \"state\": \"RESERVED\", \"description\": \"Tiene tareas en proceso asignandole stock\"}, \"granel\": 0, \"street\": {\"id\": 1, \"code\": \"100\", \"pallets\": 0, \"sector_id\": 1, \"description\": \"Pañol 100\", \"accept_picking\": 1}, \"street_id\": 1, \"position_type_id\": 1, \"position_state_id\": 3}, \"packaging_id\": 92, \"packaging_quantity\": 1}", 
    "finished": null 
},
Emile Bergeron
  • 17,074
  • 5
  • 83
  • 129
Walter Cejas
  • 1,924
  • 1
  • 12
  • 22
  • Actually what is your expected output and what is your current problem? – Yashvantsinh Zala Jul 26 '17 at 14:25
  • Instead of join you can do it with [This Link](https://laravel.com/docs/5.4/eloquent-relationships#one-to-many) – Gaurang Ghinaiya Jul 26 '17 at 14:28
  • I have tasks with a json column "data" in "task.data->from_position->id" I have the ids of another model "Position" I need to get every Position with his tasks (or with the count of Tasks they have) Like a hasMany relation ... – Walter Cejas Jul 26 '17 at 14:31
  • 1
    Trying to make this relation work using the query builder.. this returns always null arrays.. class Position extends Model { public function tasks() { return $this->hasMany(Task::class, 'data->to_position->id', 'id'); } } – Walter Cejas Jul 26 '17 at 14:45
  • I properly formatted the JSON, but you should make sure I didn't change the data accidentally. – Emile Bergeron Jul 26 '17 at 15:05
  • what about raw query and [something like this](https://stackoverflow.com/questions/39818296/using-mysql-json-field-to-join-on-a-table) – skido Jul 26 '17 at 15:11
  • I created a package for this: https://github.com/staudenmeir/eloquent-json-relations – Jonas Staudenmeir Nov 20 '18 at 04:19

0 Answers0