Search into several databases and show all results in one table

Ask the community
, , ,
1

Hello !
I’m very new to Wix and I’ve been stuck on two things for my Search page.

Objective : Have a search bar for user input that searches in several databases/collections at the same time and show all results in one table.

Each database/collection is basically a set list for stickers with the sticker numbers, names type and image. Each database/collection item have the same datapath.

My code below works for one database, it displays everything correctly in the table however the field “setname” doesn’t show for some reason (set name in the collection is a reference from another database listing the basic info of all the sets)

//this code should be in your search page
import wixData from 'wix-data'; 

$w.onReady(function () { 
  //TODO: import wixData from 'wix-data'; 
});

export function search(event) {   
  wixData.query('Toppu1997')
     .or(wixData.query('Amada3'))
   .contains('title', $w('#searchinput').value)
   .find()
   .then(res => {   
      $w('#resultsTable').rows = res.items; 
    });
} 

$w.onReady(function () {

    $w("#resultsTable").columns = [

       {

          "id": "col1",
          "dataPath": "stickernumber",
          "label": "Sticker Number",
          "width": 100,
          "visible": true,
          "type": "number"
        } ,

        {
          "id": "col2",
          "dataPath": "title",
          "label": "Name",
          "width": 100,
          "visible": true,
          "type": "string"

        },

        {

          "id": "col3",
          "dataPath": "Stickersetlist.title",
          "label": "Set Name",
          "width": 100,
          "visible": true,
          "type": "string"
        },
 {

          "id": "col4",
          "dataPath": "manu_year",
          "label": "Year",
          "width": 80,
          "visible": true,
          "type": "Number",
        } ,
        {

          "id": "col5",
          "dataPath": "type",
          "label": "Type",
          "width": 100,
          "visible": true,
          "type": "string",
        } ,
        {
          "id": "col6",
          "dataPath": "image",
          "label": "Image",
          "width": 300,
          "visible": true,
          "type": "image",
        } ,

    ];

});

I’ve found some other code in the wix forums that allows to search multiple fields in multiple databases, but I can’t seem to find how to display all results stored in the console.log into a table. I don’t know if there’s a way to fuse both of these codes.

import wixData from 'wix-data'  

$w.onReady(function () {      
$w('#searchButton1').onClick(function () {          
wixData.query("firstCollectionName")             
.contains("collectionFieldKey1", $w("#searchInput1").value)             
.or(wixData.query("firstCollectionName")                 
.contains("collectionFieldKey2", $w("#searchInput1").value))             
.find()             
.then((results1) => {                 

let results1Items = results1.items;                 
console.log(results1Items);                  

wixData.query("secondCollectionName")                     
.contains("collectionFieldKey1", $w("#searchInput1").value)                     
.find()                     
.then((results2) => {                         
let results2Items = results2.items;                         
console.log(results2Items);                          
const allResults = results1Items.concat(results2Items);                          
console.log(allResults);                      
})              
})    
})  
})

Is my objective possible or is it better to make one giant database with thousands of items to search in ?

Have a great day,

Dominic

2

You can do it.
basically run 2 queries together:

Promise.all([
    query1.find(),
    query2.find()
])
.then(r => {
allResults = r[0].items.concat(r[1].items);
//continue to populate the table
})

Note:
It’s better to have only one $w.onReady per page code (put everything inside).

3

Hello, thanks for answering ! I love that it’s possible.

But I don’t think I can achieve with only basic code skills.

Where do I integrate your code into my code ? I tried using it but i’m probably no where close


import wixData from 'wix-data'  

$w.onReady(function () {      
$w('#search').onClick(function () {          
wixData.query("Toppu1997")             
.contains("title", $w("#searchinput").value)             
.or(wixData.query("Toppu1997")                 
.contains("featuring_pkmn", $w("#searchinput").value))             
.find()             
.then((results1) => {                 

let results1Items = results1.items;                 
console.log(results1Items);                  

wixData.query("Amada3")                     
.contains("title", $w("#searchinput").value) 
.or(wixData.query("Amada3")                 
.contains("featuring_pkmn", $w("#searchinput").value))                     
.find()                     
.then((results2) => {                         
let results2Items = results2.items;                         
console.log(results2Items);                          
const allResults = results1Items.concat(results2Items);                          
console.log(allResults);                      
})              
.then(allResults => {   
      $w('#resultsTable').rows = console.log(allResults); 
    });
})    
})  
    $w("#resultsTable").columns = [

       {

          "id": "col1",

          "dataPath": "stickernumber",

          "label": "Sticker Number",

          "width": 100,

          "visible": true,

          "type": "number"

        } ,

        {

          "id": "col2",

          "dataPath": "title",

          "label": "Name",

          "width": 100,

          "visible": true,

          "type": "string"

        },

        {

          "id": "col3",

          "dataPath": "Stickersetlist.title",

          "label": "Set Name",

          "width": 100,

          "visible": true,

          "type": "string"

        },
 {

          "id": "col4",

          "dataPath": "manu_year",

          "label": "Year",

          "width": 80,

          "visible": true,

          "type": "Number",


        } ,
        {

          "id": "col5",

          "dataPath": "type",

          "label": "Type",

          "width": 100,

          "visible": true,

          "type": "string",


        } ,
        {

          "id": "col6",

          "dataPath": "image",

          "label": "Image",

          "width": 300,

          "visible": true,

          "type": "image",


        } ,

    ];

});
4 
import wixData from 'wix-data'  
$w.onReady(function () { 
 $w("#resultsTable").columns = [/*put here all the columns as you did*/];
 const query1 = wixData.query("Toppu1997")             
   .contains("title", $w("#searchinput").value)             
   .or(wixData.query("Toppu1997")                 
   .contains("featuring_pkmn", $w("#searchinput").value));

const query2 = wixData.query("Amada3")                     
  .contains("title", $w("#searchinput").value) 
  .or(wixData.query("Amada3")                 
  .contains("featuring_pkmn", $w("#searchinput").value));
   Promise.all([
      query1.find(),
      query2.find()
  ])
  .then(r => {
    $w("#resultsTable").rows = r[0].items.concat(r[1].items);
  })
})
5

P.S. but since you’re using uer input, you may want to run the queries in onClick event handler.
Like:

import wixData from 'wix-data'  
$w.onReady(function () { 
 $w("#resultsTable").columns = [/*put here all the columns as you did*/];
$w('#searchButton1').onClick(() => { 
 const query1 = wixData.query("Toppu1997")             
   .contains("title", $w("#searchinput").value)             
   .or(wixData.query("Toppu1997")                 
   .contains("featuring_pkmn", $w("#searchinput").value));

const query2 = wixData.query("Amada3")                     
  .contains("title", $w("#searchinput").value) 
  .or(wixData.query("Amada3")                 
  .contains("featuring_pkmn", $w("#searchinput").value));

   Promise.all([
      query1.find(),
      query2.find()
  ])
  .then(r => {
    $w("#resultsTable").rows = r[0].items.concat(r[1].items);
  })
})
})
6

@jonatandor35 Thanks so much ! I tried it and it works ! i’ll try to add some more databases to the code during the day !

Thanks so much again :grin:

7

@dominic-dearlove You’re welcome.
And f you like the code to be more compact (it won’t affect performance in any noticeable way), you can do:

import wixData from 'wix-data'  
$w.onReady(function () { 
 $w("#resultsTable").columns = [/*put here all the columns as you did*/];
$w('#searchButton1').onClick(() => { 
const queries = ["Toppu1997", "Amada3"].map(collection => wixData.query(collection)             
   .contains("title", $w("#searchinput").value)             
   .or(wixData.query(collection)                 
   .contains("featuring_pkmn", $w("#searchinput").value));
);
   Promise.all(queries.map(q => q.find()))
  .then(r => {
    $w("#resultsTable").rows = r[0].items.concat(r[1].items);
  })
})
})
8

@jonatandor35 Amazing thanks !

I tried the the more condensed code but it returns some small errors :


$w('#search').onClick(() => { 
const queries = ["Toppu1997", "Amada3"].map(collection => wixData.query(collection)             
   .contains("title", $w("#searchinput").value)             
   .or(wixData.query(collection)                 
   .contains("featuring_pkmn", $w("#searchinput").value)));

   Promise.all(queries.map(q => q.find())

  .then(r => {
    $w("#resultsTable").rows = r[0].items.concat(r[1].items);
  })
},)
})

For the code above i have these errors (highlighted in orange) " Property then does not exist on type 'Promise[].
And also a small error at the bottom ‘,’ expected, parsing error : unexpected token ).
When I delete the “)” I get another error below “Argument expression expected”.

Two last questions :

  • If I wish to add more databases to search into, in the code, how to I proceed, do I just add to the list ? ex : const queries = [“Toppu1997”, “Amada3”, “Amada4”].map

  • In my table there’s one colomn that doesn’t show any results, it’s blank everytime. The field in the collection is a reference field from another database, is there anyway to show that information ?

        {
"id": "col3",
"dataPath": "setname",
"label": "Set Name",
"width": 100,
"visible": true,
"type": "string"
},

Thank you !!

9

@dominic-dearlove yes. it should have 1 more )

  Promise.all(queries.map(q => q.find()))
  .then(r => {
    $w("#resultsTable").rows = r[0].items.concat(r[1].items);
  })

and no comma between },)

As for your questions:

  1. Yes. also add .concat(r[2].items) to the results.

  2. Maybe try to add to your query:

 .contains("featuring_pkmn", $w("#searchinput").value)));
.include('setName')

and for the column datapath try: ‘setName.title’ (instead of ‘.title’ use the field key as appear in the collection you refer to).

10

@jonatandor35 Thanks again !

I get an error when i add “.include” into the code (cannot find name “include”, declaration or statement expected) and when I add .concat(r[2].items) to the results I only get results for two of the databases (the first one, and the third one), when it’s r[1], i get search results for the first and second database. I dont know if I added it correctly.

$w('#search').onClick(() => { 
const queries = ["Toppu1997", "ToppuSealGum1", "RetsudenHyperStickerCollection1"].map(collection => wixData.query(collection)             
   .contains("title", $w("#searchinput").value)             
   .or(wixData.query(collection)                 
   .contains("featuring_pkmn", $w("#searchinput").value)));
   .include('setName')

   Promise.all(queries.map(q => q.find()))

  .then(r => {
    $w("#resultsTable").rows = r[0].items.concat(r[2].items);

  })
})
})

Thank you :checkered_flag: