Error with distinct

Ask the community
,
1

Hi All. Can anyone tell me why the top part of my code works but the bottom part with the distinct does not? The only difference is the distinct.

I get this error
_wixData2.default.query(…).contains(…).lt(…).gt(…).distinct(…).count is not a function

export async function filter_click(event, $w) {
$w( “#count” ).show();
$w( “#centers” ).show();

wixData.query( 'training_progrss' ) 
    .contains( 'trainer' , $w( "#staff" ).value) 
    .lt( 'training_date' , $w( '#toDate' ).value) 
    .gt( 'training_date' , $w( '#fromDate' ).value) 
    .count() 
    .then((num) => { 
        $w( "#count" ).text =  'Total Trainings: '  + num; //This works 
    }); 

wixData.query( 'training_progrss' ) 
    .contains( 'trainer' , $w( "#staff" ).value) 
    .lt( 'training_date' , $w( '#toDate' ).value) 
    .gt( 'training_date' , $w( '#fromDate' ).value) 
    .distinct( 'center_id' ) 
    .count() 
    .then((num2) => { 
        $w( "#centers" ).text =  'Total Centers Trained: '  + num2;//this does not work 
    });

}

2

Apparently, you can’t use the count and distinct functions together, and there is no need to. Distinct function results include a totalCount property.

wixData.query('training_progrss')        
 .contains('trainer', $w("#staff").value)        
 .lt('training_date', $w('#toDate').value)        
 .gt('training_date', $w('#fromDate').value)        
 .distinct('center_id')        
 .then((results) => {            
   $w("#centers").text = 'Total Centers Trained: ' + results.totalCount;
 });
3

Thanks for the help. That makes total sense.

4

So now I’m trying to dump the data into a repeater and again, I’m a bit confused. From what I understand I am pulling distinct items but I am getting an error that reads" Wix code SDK error: Each item in the items array must have a member named _id which contains a unique value identifying the item. ". From what I can tell the criteria has been met so I don’t understand the error. I do have an _id for each item and distinct should make them unique I thought. Console log shows me 4 unique items.

wixData.query( 'training_progrss' ) 
    .contains( 'trainer' , $w( "#staff" ).value) 
    .lt( 'training_date' , $w( '#toDate' ).value) 
    .gt( 'training_date' , $w( '#fromDate' ).value) 
    .distinct( 'center_id' ) 
    .then((num2) => { 
        $w( "#centers" ).text =  ''  + num2.totalCount; 
        $w( '#centersRepeater' ).data = num2.items; 
         console.log(num2.items) 
    });

}

5

You can’t assign a result of .distinct() to repeater.
The reason:
a repeater only accepts an array of objects (and with an _id field).
the result.itmes of distinct is an array of strings (or primitives) and have no _id.

You should use .find() instead of distinct, and filter out the duplicates.
Or if you only need the distinct values themselves, then you can map them into objects and assign. Something like:

let data = results.items.map((e,i) => ({_id: i.toString(), center_id = e}));
$w("#reapeater1").data = data;
6

@jonatandor35 That was my original issue. I was using find but couldnt figure out how to get rid of the dupes so I figured distinct would work. I’m used to writing sql which is a lot easier than this stuff.

7

@bill65358 See my previous post for cases you only need the center_id values (using .dsitinct()).
If you need the whole object but want to filter out duplicate center_id values, then use .find() and filter out. Like:
//…

let items = res.items;
items = items.filter((e,i) => items.findIndex(q => q.center_id === e.center_id ) === i);
8

@jonatandor35 That helps a little. My repeater shows up with the correct number of rows but each row is blank. My code queries the training_progrss collection which has the center_id field. center_id is a reference field linked to the centers collection. In the centers collection I have a title field. All I really want is to have that title field from centers to populate the repeater. My Repeater is linked to the training_progrss collection. and the text object is connected to center_id:title(text). Is the repeater blank because center_id is a reference field?

wixData.query( 'training_progrss' ) 
    .contains( 'trainer' , $w( "#staff" ).value) 
    .lt( 'training_date' , $w( '#toDate' ).value) 
    .gt( 'training_date' , $w( '#fromDate' ).value) 
    .find() 
    .then((num2) => {

let items = num2.items;
items = items.filter((e, i) => items.findIndex(q => q.center_id === e.center_id) === i);
$w( ‘#centersRepeater’ ).data = items;
console.log(items)
});

9

@bill65358 you also need to bind the data to the the repeater sub-elements.

$w("#centersRepeater").onItemReady(($item, itemData) => {
$item("#image1").src = itemData.image;
$item("#text1").text = itemData.title;
$item("#button1").link = itemData.link;
//etc...
})
10

@jonatandor35 Wow, getting so close. My repeater is now showing the center_id but what I need is the title field from the referenced collection. How would I dig down another level to get the title.?

wixData.query( 'training_progrss' ) 
    .contains( 'trainer' , $w( "#staff" ).value) 
    .lt( 'training_date' , $w( '#toDate' ).value) 
    .gt( 'training_date' , $w( '#fromDate' ).value) 
    .find() 
    .then((results) => {

let items = results.items;
items = items.filter((e, i) => items.findIndex(q => q.center_id === e.center_id) === i);
$w( ‘#centersRepeater’ ).data = items;
$w( “#centersRepeater” ).onItemReady(($item, itemData) => {
$item( “#center” ).text = itemData.center_id;

            console.log(items) 
        })
11

@bill65358 You’ll need to use .include() in your query.
See here:
https://www.wix.com/corvid/reference/wix-data/wixdataquery/include

12

Well, now I have the center names showing again but I’m back to having duplicates. I’m doing something wrong.

wixData.query( 'training_progrss' ) 
    .include( 'center_id' ) 
    .contains( 'trainer' , $w( "#staff" ).value) 
    .lt( 'training_date' , $w( '#toDate' ).value) 
    .gt( 'training_date' , $w( '#fromDate' ).value) 
    .find() 
    .then((results) => {

let items = results.items;
items = items.filter((e, i) => items.findIndex(q => q.center_id === e.center_id) === i);
$w( ‘#centersRepeater’ ).data = items;
$w( “#centersRepeater” ).onItemReady(($item, itemData) => {
$item( “#center” ).text = itemData.center_id.title;
console.log(itemData);
})
})

}

13

If you have duplicates it means you haven’t filtered by the correct field.
In your code you omitted duplicates based on " center_id “, but maybe you wish to omit duplicates based on " center_id.title”. So adjust the filter in accordance.

14

@jonatandor35 Yes! Thanks so much for your help!

15

You’re welcome :slight_smile:

16

Once i also wrote a little example for distinct .
J.D. can you take a look and tell me if all the written info is right in this little example? (if you find the time to do it) THX.

https://russian-dima.wixsite.com/meinewebsite/test-test-test

17

It looks OK (but it cannot be used in the case that Bill brought up).
(But it could a little bit shorter if you used map() for example and other short options. But it’s fine).

18

@jonatandor35
Yeahhhhh :rofl:, i just replied you in another thread to the topic —> .map -function😁
You will surely already have seen it :wink:

THX, for checking.

19

@jonatandor35 The last part I have to this project, now that I have the repeater loading the center names, is to show how many times the center_id shows up in the training_progrss collection for each center within the repeater. Any advice, hints, links to specific api would be greatly appreciated. My Very simple aproach below is of course showing the same number (7) for all centers.

wixData.query( 'training_progrss' ) 
    .include( 'center_id' ) 
    .contains( 'trainer' , $w( "#staff" ).value) 
    .lt( 'training_date' , $w( '#toDate' ).value) 
    .gt( 'training_date' , $w( '#fromDate' ).value) 
    .find() 
    .then((results) => {

let items = results.items;

        items = items.filter((e, i) => items.findIndex(q => q.center_id.title === e.center_id.title) === i); 
        $w( '#centersRepeater' ).data = items; 
        $w( "#centersRepeater" ).onItemReady(($item, itemData) => { 
            $item( "#center" ).text = itemData.center_id.title; 
            $item( "#url" ).text = itemData.center_id.subdomain; 
            $item( "#link" ).link = itemData.center_id.subdomain; 
              $item( "#centerTrainings" ).text =  ''  + results.totalCount;//This part here I'm not sure about. It shows 7 for all centers 
            console.log(itemData.center_id.title); 
        }) 
    })