What is the right way to make a database searchable?












0















I'm trying to make my database searchable. Some of my tables are products, categories, subCategories manufacturedYear, model, brands. I'm using "%LIKE%" on my query and then union on tables. I get a result back, but one of the problems I cannot get the table name. One solution I thought of is creating a searchable table with 4 columns:



id, searchable term (it could be a sku,category ,subcategory etc ) and ID of the searchable term.






php mysql laravel







share|improve this question

























  • UNION ALL SELECT 'mytable' AS tablename, term FROM ... You can put a simple string value in with a virtual field in each sub select of the union.

    – ArtisticPhoenix
    Dec 31 '18 at 21:00
















0















I'm trying to make my database searchable. Some of my tables are products, categories, subCategories manufacturedYear, model, brands. I'm using "%LIKE%" on my query and then union on tables. I get a result back, but one of the problems I cannot get the table name. One solution I thought of is creating a searchable table with 4 columns:



id, searchable term (it could be a sku,category ,subcategory etc ) and ID of the searchable term.






php mysql laravel







share|improve this question

























  • UNION ALL SELECT 'mytable' AS tablename, term FROM ... You can put a simple string value in with a virtual field in each sub select of the union.

    – ArtisticPhoenix
    Dec 31 '18 at 21:00














0












0








0








I'm trying to make my database searchable. Some of my tables are products, categories, subCategories manufacturedYear, model, brands. I'm using "%LIKE%" on my query and then union on tables. I get a result back, but one of the problems I cannot get the table name. One solution I thought of is creating a searchable table with 4 columns:



id, searchable term (it could be a sku,category ,subcategory etc ) and ID of the searchable term.






php mysql laravel







share|improve this question
















I'm trying to make my database searchable. Some of my tables are products, categories, subCategories manufacturedYear, model, brands. I'm using "%LIKE%" on my query and then union on tables. I get a result back, but one of the problems I cannot get the table name. One solution I thought of is creating a searchable table with 4 columns:



id, searchable term (it could be a sku,category ,subcategory etc ) and ID of the searchable term.






php mysql laravel




php mysql laravel






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 31 '18 at 21:54









braaterAfrikaaner

1,085517




1,085517










asked Dec 31 '18 at 20:55









Cristian AndesCristian Andes

1




1













  • UNION ALL SELECT 'mytable' AS tablename, term FROM ... You can put a simple string value in with a virtual field in each sub select of the union.

    – ArtisticPhoenix
    Dec 31 '18 at 21:00



















  • UNION ALL SELECT 'mytable' AS tablename, term FROM ... You can put a simple string value in with a virtual field in each sub select of the union.

    – ArtisticPhoenix
    Dec 31 '18 at 21:00

















UNION ALL SELECT 'mytable' AS tablename, term FROM ... You can put a simple string value in with a virtual field in each sub select of the union.

– ArtisticPhoenix
Dec 31 '18 at 21:00





UNION ALL SELECT 'mytable' AS tablename, term FROM ... You can put a simple string value in with a virtual field in each sub select of the union.

– ArtisticPhoenix
Dec 31 '18 at 21:00












2 Answers
2






active

oldest

votes


















1














You can do this:



...UNION ALL SELECT 'mytable' AS tablename, term FROM mytable ... Basically, you can put a simple string value in with a virtual field in each sub select of the union. In this case I just called it tablename with a value of "mytable" the name of this example table.



So each row in mytable will have a field added named tablename with a string value of mytable. Then as you union the results, each result set (Sub-Select) could have a different string value (in tablename) in the query for their respective tables.



You can also refer to this post which is specifically on "virtual columns"



How to create virtual column using MySQL SELECT?



I don't know if I would say this is the "Right" way, but it will surly work.



Enjoy






share|improve this answer


























  • Thanks! union all fixed my issue

    – Cristian Andes
    Jan 3 at 15:49



















0














Since you are using UNION already, why not just add the name of the table being queried as a column in each UNIONed subquery, like :



SELECT 'table1' AS table_name, id, field1, field2

FROM table1

WHERE value LIKE ?

UNION

SELECT 'table2' AS table_name, id, field11, field12

FROM table2

WHERE value LIKE ?

UNION

...


When you get the data, you can get the name of the table from where each record was returned by checking column table_name.



NB : it would also be a good idea to return the primary key of each row, to make possible further requests easier.






share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53991351%2fwhat-is-the-right-way-to-make-a-database-searchable%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    You can do this:



    ...UNION ALL SELECT 'mytable' AS tablename, term FROM mytable ... Basically, you can put a simple string value in with a virtual field in each sub select of the union. In this case I just called it tablename with a value of "mytable" the name of this example table.



    So each row in mytable will have a field added named tablename with a string value of mytable. Then as you union the results, each result set (Sub-Select) could have a different string value (in tablename) in the query for their respective tables.



    You can also refer to this post which is specifically on "virtual columns"



    How to create virtual column using MySQL SELECT?



    I don't know if I would say this is the "Right" way, but it will surly work.



    Enjoy






    share|improve this answer


























    • Thanks! union all fixed my issue

      – Cristian Andes
      Jan 3 at 15:49
















    1














    You can do this:



    ...UNION ALL SELECT 'mytable' AS tablename, term FROM mytable ... Basically, you can put a simple string value in with a virtual field in each sub select of the union. In this case I just called it tablename with a value of "mytable" the name of this example table.



    So each row in mytable will have a field added named tablename with a string value of mytable. Then as you union the results, each result set (Sub-Select) could have a different string value (in tablename) in the query for their respective tables.



    You can also refer to this post which is specifically on "virtual columns"



    How to create virtual column using MySQL SELECT?



    I don't know if I would say this is the "Right" way, but it will surly work.



    Enjoy






    share|improve this answer


























    • Thanks! union all fixed my issue

      – Cristian Andes
      Jan 3 at 15:49














    1












    1








    1







    You can do this:



    ...UNION ALL SELECT 'mytable' AS tablename, term FROM mytable ... Basically, you can put a simple string value in with a virtual field in each sub select of the union. In this case I just called it tablename with a value of "mytable" the name of this example table.



    So each row in mytable will have a field added named tablename with a string value of mytable. Then as you union the results, each result set (Sub-Select) could have a different string value (in tablename) in the query for their respective tables.



    You can also refer to this post which is specifically on "virtual columns"



    How to create virtual column using MySQL SELECT?



    I don't know if I would say this is the "Right" way, but it will surly work.



    Enjoy






    share|improve this answer















    You can do this:



    ...UNION ALL SELECT 'mytable' AS tablename, term FROM mytable ... Basically, you can put a simple string value in with a virtual field in each sub select of the union. In this case I just called it tablename with a value of "mytable" the name of this example table.



    So each row in mytable will have a field added named tablename with a string value of mytable. Then as you union the results, each result set (Sub-Select) could have a different string value (in tablename) in the query for their respective tables.



    You can also refer to this post which is specifically on "virtual columns"



    How to create virtual column using MySQL SELECT?



    I don't know if I would say this is the "Right" way, but it will surly work.



    Enjoy







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Dec 31 '18 at 21:12

























    answered Dec 31 '18 at 21:01









    ArtisticPhoenixArtisticPhoenix

    15.8k11223




    15.8k11223













    • Thanks! union all fixed my issue

      – Cristian Andes
      Jan 3 at 15:49



















    • Thanks! union all fixed my issue

      – Cristian Andes
      Jan 3 at 15:49

















    Thanks! union all fixed my issue

    – Cristian Andes
    Jan 3 at 15:49





    Thanks! union all fixed my issue

    – Cristian Andes
    Jan 3 at 15:49













    0














    Since you are using UNION already, why not just add the name of the table being queried as a column in each UNIONed subquery, like :



    SELECT 'table1' AS table_name, id, field1, field2
    
FROM table1
    
WHERE value LIKE ?
    
UNION
    
SELECT 'table2' AS table_name, id, field11, field12
    
FROM table2
    
WHERE value LIKE ?
    
UNION
    
...


    When you get the data, you can get the name of the table from where each record was returned by checking column table_name.



    NB : it would also be a good idea to return the primary key of each row, to make possible further requests easier.






    share|improve this answer




























      0














      Since you are using UNION already, why not just add the name of the table being queried as a column in each UNIONed subquery, like :



      SELECT 'table1' AS table_name, id, field1, field2
      
FROM table1
      
WHERE value LIKE ?
      
UNION
      
SELECT 'table2' AS table_name, id, field11, field12
      
FROM table2
      
WHERE value LIKE ?
      
UNION
      
...


      When you get the data, you can get the name of the table from where each record was returned by checking column table_name.



      NB : it would also be a good idea to return the primary key of each row, to make possible further requests easier.






      share|improve this answer


























        0












        0








        0







        Since you are using UNION already, why not just add the name of the table being queried as a column in each UNIONed subquery, like :



        SELECT 'table1' AS table_name, id, field1, field2
        
FROM table1
        
WHERE value LIKE ?
        
UNION
        
SELECT 'table2' AS table_name, id, field11, field12
        
FROM table2
        
WHERE value LIKE ?
        
UNION
        
...


        When you get the data, you can get the name of the table from where each record was returned by checking column table_name.



        NB : it would also be a good idea to return the primary key of each row, to make possible further requests easier.






        share|improve this answer













        Since you are using UNION already, why not just add the name of the table being queried as a column in each UNIONed subquery, like :



        SELECT 'table1' AS table_name, id, field1, field2
        
FROM table1
        
WHERE value LIKE ?
        
UNION
        
SELECT 'table2' AS table_name, id, field11, field12
        
FROM table2
        
WHERE value LIKE ?
        
UNION
        
...


        When you get the data, you can get the name of the table from where each record was returned by checking column table_name.



        NB : it would also be a good idea to return the primary key of each row, to make possible further requests easier.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 31 '18 at 21:06









        GMBGMB

        13.1k2824




        13.1k2824






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53991351%2fwhat-is-the-right-way-to-make-a-database-searchable%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            Monofisismo

            Image
            Monofisismo (do grego: monos - "único, singular" e physis - "natureza") é o ponto de vista cristológico que defende que, depois da união do divino e do humano na encarnação histórica, Jesus Cristo, como encarnação do Filho ou Verbo ( Logos ) de Deus, teria apenas uma única "natureza", a divina, e não uma síntese de ambas. O monofisismo é contraposto pelo diofisismo (ou "diafisismo"), que defende que Jesus preservou em si as duas naturezas. Historicamente, o monofisismo se refere primordialmente à posição dos que (especialmente no Egito e, em menor grau, na Síria) rejeitaram as decisões do Concílio de Calcedônia em 451 (o quarto concílio ecumênico). Os membros mais moderados entre eles, porém, defendem a teologia "miafisita", que se tornou a oficial para as Igrejas Ortodoxas Orientais. Muitos ortodoxos orientais, porém, rejeitam essa classificação, mesmo como um termo genérico, mas ele é amplamente utilizado na literatura históri...
            Read more

            Angular Downloading a file using contenturl with Basic Authentication

            Image
            0 I have a contentUrl when user clicks on that,file should be downloaded. If I try to browse the contentUrl in the browser it asks for Userid and password.I believe this is Basic Authentication.I dont want user to enter UserId and Password so I am passing it Authorization(Please see below code). Url looks some thing like this: http://XXX.XXX.XX.XXX:8080/flowable-rest/service/runtime/tasks/90875/attachments/90555/content let username : string = 'admin'; let pwd : string = 'test'; let headers = new Headers(); headers.set('Accept', 'text/json'); headers.append('Content-Type', 'application/json'); headers.set('Access-Control-Allow-Origin', '*'); headers.set('Access-Control-Allow-Methods','POST, GET, OPTIONS, PUT, DELETE'); head...
            Read more

            Olmecas

            Image
            Monumento 1, uma das quatro cabeças colossais encontradas em La Venta, com altura aproximada de 3 metros Olmecas é a designação do povo e da civilização que estiveram na origem da antiga cultura pré-colombiana da Mesoamérica e que se desenvolveram nas regiões tropicais do centro-sul do atual México durante o pré-clássico, próximo de onde hoje estão localizados os estados mexicanos de Veracruz e Tabasco, no Istmo de Tehuantepec, numa zona designada área nuclear olmeca. A cultura olmeca floresceu nesta região aproximadamente entre 1500 e 400 a.C., [ 1 ] e crê-se que tenha sido a civilização-mãe de todas as civilizações mesoamericanas que se desenvolveram posteriormente. [ 2 ] No entanto, desconhece-se a sua exacta filiação étnica, ainda que existam numerosas hipóteses colocadas para tentar resolver esta questão. O etnónimo olmeca foi cunhado pelos arqueólogos do século XX, e não devem confundir-se com os muito posteriores olmecas-xicalancas que ocuparam vários locais do México...
            Read more