Help with filtering database with code

ar1 · June 24, 2020, 8:44am

Hi, I have a database with 15,000+ rows. I’m trying to filter based on the below code, however when I click on the ‘viewresultsbutton’ nothing happens.

Would appreciate any help with this!

export function viewresultsbutton_click(event, $w) {

$w( “#dataset2” ).setFilter(wixData.filter()

   .contains( "band" , $w( '#banddrop' ).value) 
   .contains( "Size" , $w( '#sizedrop' ).value) 
   .contains( "age" , $w( '#agerb' ).value) 
   .contains( "Fit" , $w( '#fitrb' ).value) 
   .contains( "band2" , $w( '#band2rb' ).value) 
   .contains( "Fit2" , $w( '#fit2rb' ).value) 
   .contains( "hook" , $w( '#hookrb' ).value)

)

.then((results) => {
console.log( “Dataset is now filtered” );
$w( “#repeater1” ).data = results.items;
}). catch ((err) => {
console.log(err);
})

$w( “#repeater1” ).expand();
}

CODE-NINJA · June 24, 2020, 8:49am

Hello AR,

take a look at this example here…

$w.onReady(function () { });

function setMyFilter (parameter) {
 let filter =  wixData.filter()  
 let myKategory = $w('#dropdown1').value
 let myTutorial = $w('#dropdown2').value
 
 if ($w('#dropdown1').value!=0) {filter = filter.eq('kategorie', myKategory);}
 if ($w('#dropdown2').value!=0) {filter = filter.eq('tutorialLevel', myTutorial);}
    $w('#dataset1').setFilter(filter)
}

export function dropdown1_change(event) {setMyFilter()}
export function dropdown2_change(event) {setMyFilter()}

You can expand & modify the code by your needs.

ar1 · June 24, 2020, 9:02am

Hi russian-dima, thanks for this. I’m not very good at coding - let me take a crack to see if I can make this work.

anon72313111 · June 24, 2020, 9:05am

Hello

You need to use or() with your filters, without it, you’re just telling the code that you want all of them to match, that means if one of the contains() filters has no match, all of the filter will return no match, while on the other hand, using or() will return any result that matches any of the contains() filters, and not necessarily all of them.

filter = wixData.filter()
    .contains("band", $w('#banddrop').value)
    .or(
         .contains("Size", $w('#sizedrop').value)
    ).or(
         .contains("age", $w('#agerb').value)
    ).or(
         .contains("Fit", $w('#fitrb').value)
    ).or(
         .contains("band2", $w('#band2rb').value)
    ).or(
        .contains("hook", $w('#hookrb').value) 
    )

Of course you still need to call the filter function on the dataset, you can use any trigger to call it.

$w("#dataset2").setFilter(filter);

Hope that helped~!
Ahmad

ar1 · June 24, 2020, 9:16am

Thanks for this Ahmad.

I’m looking to have the ‘and’ function for each of the filters. So it narrows down to a row in the database where all the column filters are in place.

I tried testing the code by only keeping one ‘contains’ filter in and removed the rest, even that doesn’t work.

Do you know why this might be happening?

CODE-NINJA · June 24, 2020, 9:35am

Your CODE should be something like this…

function setMyFilter (parameter) {
 let REFERENCE1 = "band"
 let REFERENCE2 = "Size"
 let REFERENCE3 = "age"
 let REFERENCE4 = "Fit"
 let REFERENCE5 = "band2"
 let REFERENCE6 = "Fit2"
 let REFERENCE7 = "hook"

 let ITEM1 = $w('#banddrop').value)
 let ITEM2 = $w('#sizedrop').value)
 let ITEM3 = $w('#agerb').value)
 let ITEM4 = $w('#fitrb').value)
 let ITEM5 = $w('#band2rb').value)
 let ITEM6 = $w('#fit2rb').value)
 let ITEM7 = $w('#hookrb').value)
 
 let filter =  wixData.filter() 

 if ($w('#dropdown1').value!=0) {filter = filter.eq(REFERENCE1, ITEM1);}
 if ($w('#dropdown1').value!=0) {filter = filter.eq(REFERENCE2, ITEM2);}
 if ($w('#dropdown1').value!=0) {filter = filter.eq(REFERENCE3, ITEM3);}
 if ($w('#dropdown1').value!=0) {filter = filter.eq(REFERENCE4, ITEM4);}
 if ($w('#dropdown1').value!=0) {filter = filter.eq(REFERENCE5, ITEM5);}
 if ($w('#dropdown1').value!=0) {filter = filter.eq(REFERENCE6, ITEM6);}
 if ($w('#dropdown1').value!=0) {filter = filter.eq(REFERENCE7, ITEM7);}
 
    $w('#dataset2').setFilter(filter)
}

All you have to do now, is to modify your INPUTs and the event-handler.
In this example i use DropDown-Menus and the EVENT-HANDLER is (onChange).

export function dropdown1_change(event) {setMyFilter()}
export function dropdown2_change(event) {setMyFilter()}

Here you can see a little example…
https://russian-dima.wixsite.com/meinewebsite/blank-3

ar1 · June 24, 2020, 9:47am

Thanks Russian-dima!

I checked out your example - I’m trying to link my output to a repeater (I can’t link that database here as it has so many rows).

Is there any code i need to add for the repeater?

anon72313111 · June 24, 2020, 9:49am

I noticed that you’re passing a (results) to the next part after filtering the dataset, which is wrong because the dataset filter doesn’t pass a result parameter to the next part, so everything will be undefined and causing a lot or errors.

You can’t do this, since results is undefined, you’ll get an error saying "cannot read property items of undefined.

.then((results) => {
    console.log("Dataset is now filtered");
    $w("#repeater1").data = results.items;
}).catch((err) => {
    console.log(err);
})

What you CAN do instead is refreshing the dataset and refresh the repeater’s items, take a look at my recent comment here:

CODE-NINJA · June 24, 2020, 10:06am

My first question is, if you could implement the code to your project and wich code did you use?
I did not understand which problem you have with a repeater?
I just connected my repeater and my table in the example without any coding and everything works fine as you can see in the upated example … look here again…
https://russian-dima.wixsite.com/meinewebsite/blank-3

ar1 · June 24, 2020, 10:33am

Thanks for this Ahmad - i checked out the link you shared.

Here is the code that i used. I unfortunately can’t get it to work still

Is there something wrong with the code below or do I have to connect the dataset to items on my page?

Some other details -
- the buttons I’m using are a combination for drop-downs and radiobuttons
- my database has 15,000+ rows
- I’m connecting the filtered output to a repeater

Thanks so much for your help!

import {wixData} from ‘wix-data’ ;

export function A_change(event) {setMyFilter()}
export function B_change(event) {setMyFilter()}
export function C_change(event) {setMyFilter()}
export function D_change(event) {setMyFilter()}
export function E_change(event) {setMyFilter()}
export function F_change(event) {setMyFilter()}
export function G_change(event) {setMyFilter()}

$w.onReady( function () { });

function setMyFilter (parameter) {
let REFERENCE1 = “columntitle1”
let REFERENCE2 = " columntitle2 "
let REFERENCE3 = " columntitle3 "
let REFERENCE4 = " columntitle4 "
let REFERENCE5 = " columntitle5 "
let REFERENCE6 = " columntitle6 "
let REFERENCE7 = " columntitle7 "

let ITEM1 = $w( ‘#A’ ).value
let ITEM2 = $w( ‘#B’ ).value
let ITEM3 = $w( ‘#C’ ).value
let ITEM4 = $w( ‘#D’ ).value
let ITEM5 = $w( ‘#E’ ).value
let ITEM6 = $w( ‘#F’ ).value
let ITEM7 = $w( ‘#G’ ).value

let filter = wixData.filter()
if ($w( ‘#A’ ).value!== 0 ) {filter = filter.eq(REFERENCE1, ITEM1);}
if ($w( ‘#B’ ).value!== 0 ) {filter = filter.eq(REFERENCE2, ITEM2);}
if ($w( ‘#C’ ).value!== 0 ) {filter = filter.eq(REFERENCE3, ITEM3);}
if ($w( ‘#D’ ).value!== 0 ) {filter = filter.eq(REFERENCE4, ITEM4);}
if ($w( ‘#E’ ).value!== 0 ) {filter = filter.eq(REFERENCE5, ITEM5);}
if ($w( ‘#F’ ).value!== 0 ) {filter = filter.eq(REFERENCE6, ITEM6);}
if ($w( ‘#G’ ).value!== 0 ) {filter = filter.eq(REFERENCE7, ITEM7);}

$w( '#dataset2' ).setFilter(filter)

}
$w( ‘#viewresults’ ).onClick( async (event) => {
await setMyFilter().then(() = {
// Refresh the dataset
$w( ‘#dataset2’ ).refresh();
}).then(() = {
// Repopulate the repeater’s data
})
})
$w( ‘#repeater1’ ).forEachItem( ($item, data) => {
$item( ‘#text89’ ).text = data.columntitle8;
$item( ‘#text88’ ).text = data.columntitle9;

anon72313111 · June 24, 2020, 10:50am

A couple issues in your code:

import {wixData} from'wix-data';

it should be:

import wixData from 'wix-data';

Also, the repeater’s forEachItem() function is placed in the wrong place, I did mention in my example that you need to place it inside the second then() after refreshing the dataset, you’re even placing it outside the onClick() function.

Replace with this code:

$w('#viewresults').onClick(async(event) => {
    await setMyFilter().then(() = {
        // Refresh the dataset
        $w('#dataset2').refresh();
    }).then(() = {
        // Repopulate the repeater's data
        $w('#repeater1').forEachItem( ($item, data) => {
            $item('#text89').text = data.columntitle8;
            $item('#text88').text = data.columntitle9;
        })
    })
})

You’re also missing the }) part.

CODE-NINJA · June 24, 2020, 11:01am

The question is: Does “AR” really need the second part of code?
He could just connect his elements with his dataset and that’s it.

anon72313111 · June 24, 2020, 11:05am

russian-dima, you have a point But I don’t really know

CODE-NINJA · June 24, 2020, 11:08am

Ok. Yes, that is a little bit difficult without to know how the site is constructed and how are all the connections. We can just imagine.

anon72313111 · June 24, 2020, 11:20am

You’re %100 correct

ar1 · June 24, 2020, 11:34am

I still can’t get it to work

I’ve made the changes as per below. Is there any way one of you could take a look at my coding live? Would greatly appreciate it.

import wixData from 'wix-data'; 

export function A_change(event) {setMyFilter()} 
export function B_change(event) {setMyFilter()} 
export function C_change(event) {setMyFilter()} 
export function D_change(event) {setMyFilter()} 
export function E_change(event) {setMyFilter()} 
export function F_change(event) {setMyFilter()} 
export function G_change(event) {setMyFilter()} 

$w.onReady(function () { }); function setMyFilter (parameter) { 

let REFERENCE1 = "columntitle1" 
let REFERENCE2 = "columntitle2" let REFERENCE3 = "columntitle3" let REFERENCE4 = "columntitle4" let REFERENCE5 = "columntitle5" let REFERENCE6 = "columntitle6" let REFERENCE7 = "columntitle7" 

let ITEM1 = $w('#A').value 
let ITEM2 = $w('#B').value 
let ITEM3 = $w('#C').value 
let ITEM4 = $w('#D').value 
let ITEM5 = $w('#E').value 
let ITEM6 = $w('#F').value 
let ITEM7 = $w('#G').value let filter =  wixData.filter()  


if ($w('#A').value!==0) {filter = filter.eq(REFERENCE1, ITEM1);} if ($w('#B').value!==0) {filter = filter.eq(REFERENCE2, ITEM2);} if ($w('#C').value!==0) {filter = filter.eq(REFERENCE3, ITEM3);} if ($w('#D').value!==0) {filter = filter.eq(REFERENCE4, ITEM4);} if ($w('#E').value!==0) {filter = filter.eq(REFERENCE5, ITEM5);} if ($w('#F').value!==0) {filter = filter.eq(REFERENCE6, ITEM6);} if ($w('#G').value!==0) {filter = filter.eq(REFERENCE7, ITEM7);} 

    $w('#dataset2').setFilter(filter) } 

$w('#viewresults').onClick(async(event) => {
    await setMyFilter().then(() = {
        // Refresh the dataset
        $w('#dataset2').refresh();
    }).then(() = {
        // Repopulate the repeater's data
        $w('#repeater1').forEachItem( ($item, data) => {
            $item('#text89').text = data.columntitle8;
            $item('#text88').text = data.columntitle9;
        })
    })
})

ar1 · June 24, 2020, 12:00pm

Is there a way for you directly message you?

anon72313111 · June 24, 2020, 3:47pm

Details in my profile

Topic		Replies	Views
Database filters only occasionally Ask the community code , question	0	106	July 24, 2019
Dropdown filters->shows no results Ask the community code , question	2	201	July 21, 2019
dataset Filter code issues Ask the community code , question	31	539	May 30, 2020
Filtering from dataset Ask the community code , question	2	107	October 6, 2020
Dataset filtering help Ask the community code , question	3	140	January 15, 2019

Help with filtering database with code

Related topics