Sort results by a relationship column in Laravel 4

Question

Sort results by a relationship column in Laravel 4

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

I currently have some methods that I use together that allow me to make requests to my application controlling the "withs", "orders" and such via querystring. I can do, for example:

GET /users?sort=name&sex=female

In this case the application will return a list of female users and sorted by name.

What I need to do now is sort the results by the column of a related table. Since Laravel makes two queries when we use the with() method, sorting is only possible by a column of the model table that is searching. Is there any way I can do what I want besides setting up the query and the joins in hand by the builder? I also saw what you can do with the sortBy of the Collection, but this same code that I have to deal with pagination and, in this case, doing for collection would only sort the data that has already come from the database.

laravel-4 laravel-eloquent

asked by anonymous 27.06.2014 / 14:18

2 answers

1

I've created a package laraerp / ordination to use in my projects. It has a trait that when you add it to your model, the orderBy method is rewritten. With it you can send a request of the type:

GET /cliente?by=pessoa.razao_apelido&order=desc

In the example the table Customer has a belongsTo Person.

Follow tutorial link .

27.05.2015 / 15:06

Photos in different divs using instafeed.js Select [with added functions] group by in Lambda

score 1 · Accepted Answer

Example:

SQL creation of tables

CREATE TABLE 'creditos' (
  'creditoid' int(11) NOT NULL AUTO_INCREMENT,
  'descricao' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  'created_at' timestamp NULL DEFAULT NULL,
  'updated_at' timestamp NULL DEFAULT NULL,
  PRIMARY KEY ('creditoid'),
  UNIQUE KEY 'creditoid_UNIQUE' ('creditoid')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE 'noticias' (
  'noticiaid' bigint(20) NOT NULL AUTO_INCREMENT,
  'creditoid' int(11) NOT NULL,
  'titulo' text COLLATE utf8_unicode_ci NOT NULL,
  'subtitulo' text COLLATE utf8_unicode_ci,
  'texto' text COLLATE utf8_unicode_ci NOT NULL,
  'data' date NOT NULL,
  'hora' time NOT NULL,
  'fotocapa' varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  'status' tinyint(4) NOT NULL,
  'created_at' timestamp NULL DEFAULT NULL,
  'updated_at' timestamp NULL DEFAULT NULL,
  PRIMARY KEY ('noticiaid'),
  UNIQUE KEY 'noticiaid_UNIQUE' ('noticiaid'),
  KEY 'pk2creditoId_idx' ('creditoid'),
  CONSTRAINT 'pk2creditoId' FOREIGN KEY ('creditoid') REFERENCES 'creditos' ('creditoid') ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Models of these two tables (Eloquent)

Credit

<?php
class Credito extends Eloquent {
    public $table      = 'creditos'; 
    public $primaryKey = 'creditoid';
    public $timestamps = true;
    public function noticia()
    {
        return $this->hasMany('Noticia', 'creditoid');
    }           
    public function getDates()
    {
        return array('created_at','updated_at');
    }
}

News

<?php
class Noticia extends Eloquent {
    public $table      = 'noticias'; 
    public $primaryKey = 'noticiaid';
    public $timestamps = true;  
    public function credito()
    {
        return $this->belongsTo('Credito', 'creditoid');
    }    
    public function getDates()
    {
        return array('created_at','updated_at');
    }
}

Search for the relationship between these tables by like in the credit description and order by no Creditos.Descricao (relationship table)

Eloquent

Mode with with

Noticia::with(array('Credito' => function($query){
        $query->where('creditos.descricao', 'like', '%a%');
        $query->orderBy('creditos.descricao');
    }))->get();

Join Mode

Noticia::join('creditos', 'creditos.creditoid', '=', 'noticias.creditoid')
         ->where('creditos.descricao', 'like', '%a%')
         ->orderBy('creditos.descricao', 'desc')
         ->get();

Mode with with, join and orderBy

Noticia::with('Credito')
        ->join('creditos', 'creditos.creditoid','=', 'noticias.creditoid')
        ->orderBy('creditos.descricao')
        ->get();

Query Builder

DB::table("noticias")
        ->join('creditos', 'creditos.creditoid', '=', 'noticias.creditoid')
        ->where('creditos.descricao', 'like', '%a%')
        ->orderBy('creditos.descricao', 'desc')
        ->get();

Obs : where and orderBy have been put together on purpose, to show the various possibilities, being that you can use only what you need, which in this case would be orderBy . Another thing if you can give an opinion use with join is much more transparent

References: