Search in more then one column

Hi,

I need a little help. I’ve set up search page for my site and it is working well.
But I would like it to search in the colums ‘title’ and ‘about’ as well as ‘products’.
I’ve read through the Forum post ‘Search a Database’, read Working with the Data API and Wix Code API reference, but I’m probably not smart enough, sorry.

This is my code, what do I need to change?

import wixData from ‘wix-data’;

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

});

export function searchButton_click(event) {
let searchValue = $w(‘#searchInput’).value;
let searchWords = searchValue.split(’ ');
let query = wixData.query(‘Producers’)
.contains(‘products’, searchWords[0])
;

for (let i=1; i < searchWords.length; i++) {
query = query.or(wixData.query(‘Producers’)
.contains(‘products’, searchWords[i])
);
}

query.find()
.then(res => {
$w(‘#resultsTable’).rows = res.items;
});
}

Also I would like the results to link to the relevant pages. I know you should not connect the Database, so it probably has to be a code saying; link results to ‘link-Producers-title’ Url?

Hope you can help.
Thanks in advance.

Hi,

How about this?

wixData
  .query("Producers")
  .contains("products", searchWords[0])
  .or(wixData.query("Producers").contains("title", searchWords[0]))
  .or(wixData.query("Producers").contains("about", searchWords[0]));

Great, that works. Thank you very much.
Now I still need to get the seach results to link to the Producers individual pages.

Try this (I simplified your example):

import wixData from 'wix-data';

$w.onReady(() => {
  $w('#resultsTable').columns = [
    {
      id: 'title',
      dataPath: 'title',
      label: 'Title',
      visible: true,
      type: 'string',
      linkPath: 'link-Producers-title',
      width: 100
    }
  ];
});

export async function searchButton_click() {
  const { value } = $w('#searchInput');
  const { items } = await wixData
    .query('Producers')
    .contains('products', value)
    .or(wixData.query('Producers').contains('title', value))
    .or(wixData.query('Producers').contains('about', value))
    .find();
  $w('#resultsTable').rows = items;
}

link-Producers-title is the key of the dynamic page field. You created a dynamic page for the collection, didn’t you?

With this example, clicking on a row in the table will open the dynamic page for the producer.

Note, that instead of using wixData you can do the same via dataset: you can connect the table to the dataset and set filter on the dataset (see https://www.wix.com/code/reference/wix-dataset.html#setFilter). Also you’ll be able to conveniently configure table columns in UI instead of doing that in code.

Hi Yevhen,

Thanks for that.
I can see what you did, but I like the table how I have it better ( with all the information in it, not just the column title). People would want to search for products and read a little about the producer.

Yes I created a dynamic page for each producer (link-Producer-title) which I would like to link the result rows to. Just like you did in the example, I only want to show the other colums as well.

I see that there is another way to do it, like you said; link the database and then filter.
But that goes a little far for me at the moment. I want to get my head around this method first :slight_smile:

Okay, some things I came up with (that don’t work!)

$w.onReady(() =>
{ $w(‘#resultsTable’).columns = [ { linkPath: ‘link-Producers-title’} ]; });

Or:
export function resultsTable_dblClick(event) {
//to(/link-Producers-title)
}

Sorry, I’ve never worked with code and I’m just trying to get my head around it.

Hey,

I can see what you did, but I like the table how I have it better ( with all the information in it, not just the column title).
I only had the column Title for simplicity. Other columns can be added, too.

Just like you did in the example, I only want to show the other colums as well.
For instance, if I want to add columns Products and About :

$w.onReady(() => {
  $w('#resultsTable').columns = [
    {
      id: 'title',
      dataPath: 'title',
      label: 'Title',
      visible: true,
      type: 'string',
      linkPath: 'link-Producers-title',
      width: 100
    },
    {
      id: 'products',
      dataPath: 'products',
      label: 'Products',
      visible: true,
      type: 'string',
      linkPath: 'link-Producers-title',
      width: 100
    },
    {
      id: 'about',
      dataPath: 'about',
      label: 'About',
      visible: true,
      type: 'string',
      linkPath: 'link-Producers-title',
      width: 100
    }
  ];
});

You have to specify all the details, not just linkPath. Note, that each column has its own link. If you want all columns to have the same link, just duplicate it for each column like in the example above.

I see that there is another way to do it, like you said; link the database and then filter.

But that goes a little far for me at the moment. I want to get my head around this method first :slight_smile:
I believe the other way is simpler because you can configure table columns via GUI instead of doing that in code. The only thing you’ll need to do is to build a filter that’s very similar to what you’re doing now.

Whether you decide to take one way or the other, I suggest you to try connecting a table to the collection Producers, setting up its columns via GUI, and then checking out how the column configuration looks like:

$w.onReady(() => {
  console.log($w('#resultsTable').columns);
});

Go to preview and see the output.

Thanks for all that Yevhen. I’m going to play around with it and see if I can work it out.
I’ll get back to you if I need you again :wink:

Good luck!

Hi Yevhen, back again!
Okay, I think I figured it out. You are right about linking the database and then applying a filter, instead of using code. It is about the same as how the search function in the WIX Table Master app works.

BUT …

I would like it to look more like Google.
As in; you only see the search box (and maybe an empty list) and then only show the search results after the search instead of showing all the listings and then only the filtured ones.

So, can I hide the table via the Properties Panel (hide on load) and then show it after they hit the Search button to show the results?

Hi,

Sure, you can hide and show search results:

import wixData from 'wix-data';

export async function searchButton_click() {
  const { value } = $w('#searchInput');
  const { items } = await wixData
    .query('Producers')
    .contains('products', value)
    .or(wixData.query('Producers').contains('title', value))
    .or(wixData.query('Producers').contains('about', value))
    .find();
    
  const resultsTable = $w('#resultsTable');
  resultsTable.rows = items;
  resultsTable.show();
}

Hello Yavhen, I try the code on my website but I get an error and this message…
‘’ Wix code SDK Warning: The rows parameter that is passed to the rows method cannot be set to null or undefined. ‘’

import wixData from 'wix-data';

export function searchButton_onClick() {
  const { value } = $w('#textInput1');
  const { items } = wixData
    .query('ApplicationForm')
    .contains($w('#textInput1').value)
    .or(wixData.query('ApplicationForm').contains($w('#textInput1').value))
    .or(wixData.query('ApplicationForm').contains($w('#textInput1').value))
    .find();
    
  const resultsTable = $w('#table1');
  resultsTable.rows = items;
  resultsTable.show();
  
}

Can you help?

Hey, sure. You’re missing a couple of statements ( async/await ) and the first argument for contains (it should be a field name from your collection):

import wixData from 'wix-data';

export async function searchButton_onClick() {
  const { value } = $w('#textInput1');
  const { items } = await wixData
    .query('ApplicationForm')
    .contains('fieldName', value)
    .find();

  const resultsTable = $w('#table1');
  resultsTable.rows = items;
  resultsTable.show();
}

The statements will be highlighted as errors, but they’re not. This is a working example. The same code could be written as follows:

import wixData from 'wix-data';

export function searchButton_onClick() {
  const { value } = $w('#textInput1');
  wixData
    .query('ApplicationForm')
    .contains('fieldName', value)
    .find()
    .then(({ items }) => {
      const resultsTable = $w('#table1');
      resultsTable.rows = items;
      resultsTable.show();
    });
}

Thank you this helped, I got the code to work.

You’re welcome.

Hi Yevhen,

I am having a similar issue as the person that started this thread. I was reading through the discussion and I want to your opinion on what it would be best to either have a seprate search page or have the search function within the main page that already has all the data bind to the database.

The way I have it right now, is I have set up a seprate page for the search and below is my code and it works well but I cannot make the results on that table link to that dynamic record. I am want the results to link to the data in the database which is a dynamic page. The name of that dynamic page is: FamiliesProfiles/{Title Listing}. Title Listing is the key field I am referencing.

Ok so the code on my stand alone search page is:

import wixData from ‘wix-data’;

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

export function SearchBotton_onclick(event, $w) {
wixData.query(‘FamiliesProfiles’)
.contains(‘location’,$w(‘#SearchBox’).value)
.or(wixData.query(‘FamiliesProfiles’).contains(‘state’, $w(‘#SearchBox’).value))
.or(wixData.query(‘FamiliesProfiles’).contains(‘city’, $w(‘#SearchBox’).value))
.find()
.then(res => {
$w(‘#resultsTable’).rows = res.items;
});
}

export function resultsTable_onclick(event, $w) {
//Add your code for this event here:
}

How can I make it so the results on my resultsTable are linked to the right record on the database?

Not to muddy the water… but after reading your comments I am wondering if I would be better off having the search box on the actual directory page I created, which to displays all the data from my database in a table. This directory page, I have managed to successfully link each record to the page that displays that full record. I created a directory page that only has some fields displayed and if people want to look further into a record they click on the row and it takes them to the full page with all the data for such record. I hope I am making sense…

Thanks in advance for your help!
Gotita

Hi Gotita,

I’m trying to understand your example.

Could you please elaborate on your database structure?

From what I see in your code, you have a collection “FamiliesProfiles” with the following field keys “location”, “state”, “city”.

What is “Title Listing”. Is it the title field with display name “Title Listing” or it’s a different field? If it’s a different field, what is its key? “titleListing”?

There’s a dynamic page for “Title Listing”. Is it an item or category page?

If your site is published, you may also give me a link to it so I can explore it.

Thanks,
Yevhen

Hi Yevhen,

Yes, sorry my email was not clear…

Ok so my database “FamiliesProfiles” have several fields and Title Listing is one of the key fields. You are correct the key field is “titleListing” in the database.

Now, what you see in my javascript code are the key fields I want the user to be able to search for, which are the location, city and state. Here is the search page of the code I posted earlier, it is members only so below are the testing credentials

https://gotitag.wixsite.com/travelmonth/family-directory-search

please use this login:

test1@cox.net
password

Once you login you should see the search page, I only have one record in the database so please search using only one of these words:

san diego
carlsbad
CA

You will see in the table that I have chosen 4 key fields (columns) to display:

Title Listing
Location
City
State

After the search function does its thing it returns the one record that matches the search.

I want the row to be clickable to its dynamic page, which is, in this case it would be to this dynamic page:
https://gotitag.wixsite.com/travelmonth/FamiliesProfiles/Smith

The results in these dynamic pages are linked to the main directory. So people browsing the directory can click on each profile. The directory page is: https://gotitag.wixsite.com/travelmonth/FamiliesProfiles - This is the page where i was wondering if I should have the search box added and have a set filter as you had suggested on the other guy’s conversation (you can actually see that I started adding the input box and submit button) but decided before trying to go that route I decided to sent you a message to not get mixed up and actually find the best solution to my problem.

Anyhow, since I did not know how make the search box work in my “FamiliesProfiles” dynamic page, i created a separate search page to try to get the results I want, which again, is this page: https://gotitag.wixsite.com/travelmonth/family-directory-search

So, even though I can get the search page to search the database I do not know how to make the results click to their profiles.

The directory page (https://gotitag.wixsite.com/travelmonth/FamiliesProfiles) is connected to the dataset set but since my search page is not connected to a dataset that is where I am stuck.

I hope this explanation makes a bit more sense… let me know please.

that you again for your help!
Gotita

Hi Gotita,

It is possible to make it a link:

$w.onReady(() => {
  const resultsTable = $w('#resultsTable');
  resultsTable.columns = resultsTable.columns.map(column =>
    Object.assign(column, { linkPath: 'link-FamiliesProfiles-titleListing' })
  );
});

Every column in a table may have its own link. The code above links all the columns to the same page.

Although, it is possible to configure column links like that, I would suggest taking a different approach:

  1. Add a Dataset to the page and connect it to FamiliesProfiles.
  2. Connect the table to the dataset and configure columns and their links in UI. This will do all the necessary column configuration for you (in this case you don’t need the code above in $w.onReady).
  3. Make the table hidden on load (select it and check Hidden on Load in Properties panel — if you don’t see the panel, enable it in the Editor’s menu Tools → Properties Panel).
  4. Update the handler for the search button’s click:
export async function SearchBotton_onclick(event, $w) {
  const { value } = $w('#SearchBox');
  if (value !== '') {
    await search(value);
    $w('#resultsTable').show();
  }
}

async function search(query) {
  await $w('#dataset1').setFilter(
    wixData
      .filter()
      .contains('location', query)
      .or(wixData.filter().contains('state', query))
      .or(wixData.filter().contains('city', query))
      .or(wixData.filter().contains('titleListing', query))
  );
  $w('#resultsTable').show();
}

With a dataset you may also consider switching to repeaters instead of showing the search results in a table: https://www.wix.com/code/home/repeaters

Regarding whether to add a search page or show the search results on the directory page. I would do the following: when the search button on the directory page is clicked, go to the search page and set the search term as a query string parameter:

import wixLocation from 'wix-location';

export function submit_click(event, $w) {
  const { value } = $w('#searchBox');
  wixLocation.to(`/family-directory-search?q=${encodeURIComponent(value)}`);
}

On the search page, read the parameter from the query string and if it is present search immediately:

import wixData from 'wix-data';
import wixLocation from 'wix-location';

$w.onReady(async () => {
  const { q = '' } = wixLocation.query;
  $w('#SearchBox').value = q;

  const resultsTable = $w('#resultsTable');
  if (q !== '') {
    await search(q);
    resultsTable.show();
  } else {
    resultsTable.hide();
  }
});

export async function SearchBotton_onclick(event, $w) {
  const { value } = $w('#SearchBox');
  if (value !== '') {
    await search(value);
    $w('#resultsTable').show();
  }
}

async function search(query) {
  await $w('#dataset1').setFilter(
    wixData
      .filter()
      .contains('location', query)
      .or(wixData.filter().contains('state', query))
      .or(wixData.filter().contains('city', query))
      .or(wixData.filter().contains('titleListing', query))
  );
  $w('#resultsTable').show();
}

In this case your search page becomes more functional: you can either open it directly and search there or come there by initiating search from the directory page.

Regards,
Yevhen

Hi Yevhen,

Thanks so much for all your input. I will try to apply it and will let you know if Im successful :slight_smile:

Gotita

Hi Gotita,

You’re welcome!

The examples should work — I tested them all.

Regards,
Yevhen