Querying multiple tables, merging them and ordering by name

Hi.

The title says everything, actually. :)

I'm trying to create a basic search functionality, but there are more than one tables that I need to query. How can I include users and another additional table into my existing script and order them by name? The thing is, every table has a different name to define its names. username for users, original_name for books, etc.

    public function search(Request $request)
    {
        if (Request::ajax())
        {
            $keywords = Request::input("keywords");
            $arr = array();
            $results = Thing::where("original_name", "LIKE", "$keywords%")->orderBy('original_name', 'ASC')->take(10)->get();
            foreach($results as $result)
            {
                $arr[] = array('id' => $result->id, 'original_name' => $result->original_name);
              }

            echo json_encode($arr);
        }
    }

Here's my jQuery ajax code.

    $('#search').on('input', function () {
        var searchKeyword = $(this).val();
        if (searchKeyword.length >= 3) {
            $.post('/search', {keywords: searchKeyword}, function (data) {
                $('ul#content').empty();
                $.each(data, function () {
                    $('ul#content').append('<li><a href="example.php?id=' + this.id + '">' + this.original_name + '</a></li>');
                });
            }, "json");
        }
    });

prymribb

10 years ago

Best Answer

Level 2

@spoon If it needs to be very efficient you may need a search indexer like Solr, Elsaticsearch or Algolia.

If it is not high use you can try creating a view in SQL and union your tables:


CREATE VIEW `search_view` AS

SELECT
    "user" AS type,
    id,
    username AS name
FROM users

UNION

SELECT
    "book" AS type,
    id,
    original_name AS name
FROM books

Then in php:

class Search extends Model
{
    protected $table = 'search_view';
}

return Search::where("name",$searchterm)->orderBy("name")->get();

1 like

Please or to participate in this conversation.