Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Google Sign In with OpenID

where_search and extra columns

edited December 2014 in Framework

I definitely like where_search.

Would it be possible to extend the search to the columns extracted using subquery?

Like author_name in the following example:

    $items = books\Book::query("id, title, author, (select name from authors where authors.id = books.author) as author_name")

A solution could also be to create a model of a sql view (ie. only for select). Is that possible?

Comments

  • How about something like this?

    <?php
    
    namespace books;
    
    Class Book extends \Model {
        public $table = '#prefix#books';
    }
    
    Class Author extends \Model {
        public $table = '#prefix#authors';
    }
    
    // building the search query from `query` parameter
    $query  = isset ($_GET['query']) ? $_GET['query'] : '';
    $return = 'b.id, b.title, b.author, a.name as author_name';
    $from   = '#prefix#books b, #prefix#authors a';
    $join   = 'b.author = a.id';
    $fields = array ('b.title', 'a.author');
    
    // pager settings from `offset` parameter
    $limit  = 20;
    $num    = isset ($_GET['offset']) ? $_GET['offset'] : 1;
    $offset = ($num - 1) * $limit;
    
    $sql = \books\Book::query ($return)
        ->from ($from)
        ->where_search ($query, $fields)
        ->and_where ($join)
        ->sql ($limit, $offset);
    
    echo $sql;
    

    And here's the output:

    select b.id, b.title, b.author, a.name as author_name
    from #prefix#books b, #prefix#authors a
    where (b.title like ? or a.author like ?)
    and b.author = a.id
    limit 20 offset 0
    
  • Thank You. Perfect and already used. Sorry, my mind was elsewhere.

Sign In or Register to comment.