1

I'm building an application and I need to get result from three tables, Applications, Companies, Sectors. In Applications table I have a json_encoded array saved for sectors like ["25", "10", "8", "39"].

I'm using joins to get values from related tables but I cannot get sectors based on that array.

Any ideas on how to get this working ? Thank you!

Here is my controller query:

$data = DB::table( 'applications' )
    ->join( 'companies', 'applications.company_id', '=', 'companies.id' )
    ->join( 'states', 'applications.state_id', '=', 'states.id' )
    ->join( 'cities', 'applications.city_id', '=', 'cities.id' )
    /* ->join('sectors', 'applications.sectors', 'IN', 'sectors.id') */
    ->select('applications.*', 'companies.company_name as company_name', 'states.name as state_name', 'cities.city as city_name')
    ->orderBy('applications.id', 'asc')
    ->get();
Blackus
  • 6,883
  • 5
  • 40
  • 51
Ardian Cakolli
  • 66
  • 5
  • 1
    Well if the relationship of the table you are trying to connect is not done via normal table binding, then you need to use something else to query your table and not SQL. I strongly advice against that as it is a hustle. You better off creating an N - M relationship table and do all the join there. Just my thought of how this is going to work. – Harry Geo May 18 '15 at 14:38
  • akad0, thnx for your response, regarding table relations I think they are correctly defined with foreign keys and I am able to get correct results, except sectors which I can not relate to their proper ids in sector table. – Ardian Cakolli May 18 '15 at 14:42
  • The problem If I understand correctly is you are trying to relate a json array with some table records. SQL does not provide this kind of support, it can not "search" inside a json object. Of course it can be done easily if you break the operation in a two step process – Harry Geo May 18 '15 at 14:52
  • Yes I understand what you mean, that's true. If I convert that into searchable array like ex. WHERE `sectors`.id IN (23, 9, 3, 30) that is no more an issue. The next step is my problem, getting values from other table based on this array of ids. It could help also if I concatenate name of sectors at the end of result. – Ardian Cakolli May 18 '15 at 15:05
  • Well if you json_decode your json object and convert it into an array, you could just `$json_array = json_decode($json,true);` `$sectors= DB::table('sectors')->whereIn('id', $json_array)->get();` As you can see you can pass an array inside query builder without problem. You can [read more here](http://laravel.com/docs/5.0/queries) my friend. – Harry Geo May 18 '15 at 15:10
  • akad0 thank you for your answers, that's a solution how to get sectors separately from table but I need them somehow in one query. I need them in one row of data, when I get an application inside should contain sectors in which user applied. I have been reading documentation and doing research but basically I need an mysql way to get the data I need, since Laravel provides DB::raw('query') for this reason. – Ardian Cakolli May 18 '15 at 18:52
  • You could just pass 2 separate variables in your views. Presenting the data to the user and acquiring the data is a separate process. If you really want to query json collumns maybe try something like the below [jaQL](https://code.google.com/p/jaql/) , [jsonPath](http://goessner.net/articles/JsonPath/) , [JSONiq](http://www.jsoniq.org/) . Or you could move your database to Postgre that has the ability to query json structure. More about it [here](http://stackoverflow.com/questions/10560394/how-do-i-query-using-fields-inside-the-new-postgresql-json-datatype) – Harry Geo May 18 '15 at 19:14
  • yes maybe that's better solution to separate processes. I'm afraid I cannot change to Postgre but I'll consider using it in the future. thnx – Ardian Cakolli May 19 '15 at 06:38

0 Answers0