0

I have a use case where the client should be able to filter out the data required by names. The number of names could be in millions and the user should have access to all of them in a dropdown list. There are certain features that need to be implemented with this dropdown:

  1. Search the dropdown and get the filtered names according to the search string.
  2. Click 'Include All' from the dropdown to include all of the filtered names.
  3. Click 'Clear All' to clear all the selected ones (or a similar approach)
  4. Be able to remove a few out of those selected ones

The filtered names could be in millions so how should I implement the following functionality? Obviously getting all the data on the client side in one go is not an option as the size of the data could be 100+ mb.

The data is being fetched by nodejs/graphql from google bigquery using standard sql queries and being sent to a react based frontend.

Hardik Aswal
  • 227
  • 3
  • 15
  • 1
    use select dropdown with search box and show the options based on user search – anjaneyulubatta505 Aug 12 '21 at 06:26
  • You can try a buffered/paged store for your dropdown. – xGeo Aug 12 '21 at 06:29
  • @anjaneyulubatta505 That wouldn't be practical for millions of items since I have to use features like Include All & Clear All as I shared above. Could you go into details about how I would implement these? – Hardik Aswal Aug 12 '21 at 06:43
  • @GeomanYabes Not really familiar with that. Would my use case of Include All filtered out and Clear All filtered out be implemented? Please share some more details about this approach – Hardik Aswal Aug 12 '21 at 06:44
  • 2
    You probably need to read ["What is the best way to paginate results in SQL Server"](https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) – xGeo Aug 12 '21 at 07:29
  • Pagination is the keyword. Use it to split the payload from server to reduce stress on network and client's hardware – Phạm Huy Phát Aug 12 '21 at 07:47
  • @PhạmHuyPhát Yes pagination would definitely be required. But as I have mentioned in the question, I need to implement features like include all and clear all when a user searches for a string. For eg. If the user searches for 'Ad' and hits include all, I want to select all the names which have Ad in them eg Adam, Adrian etc. Similarly, there will be a clear all feature as well. But the problem is, if I have to select all the names which have Ad in them, I'll have to fetch them as well which is taking 60-70 seconds as of now. – Hardik Aswal Aug 12 '21 at 07:50
  • 1
    @HardikAswal in that case, only retrieve first 5-10 item that best match on user request, if user choose to see more -> click 'more'. No need to load all result on the first fetch. In the case client need a **select all** feature (without see all their result displayed on screen) - make the SELECT ALL button call another API to return all available IDs that need to be bulk-actioned with. – Phạm Huy Phát Aug 12 '21 at 08:15
  • If your query takes 60 seconds to run you need to either store your data specific to your problem if the size is huge or set the appropriate index and query accordingly. Depending on the DB the approach might differ. The results returned by the db should be paginated. – Edub Aug 12 '21 at 08:25
  • @PhạmHuyPhát I agree with the first part of your answer. But in the case of select all, returning all the matching ids would take too much time and that's what I want to reduce. In my case, names is just an example, it would actually be resources, if a user searches for resources containing ec2, and clicks include all, all the resources containing ec2 should be selected. But if I try to fetch them to my frontend, the app would most likely freeze/take a lot of time. I think there needs to be a smarter SQL query in this case instead of fetching all records. – Hardik Aswal Aug 12 '21 at 08:30
  • @Edub Query time isn't the problem, I tested the queries on the bigquery itself and the query times were like 2-3 seconds (sometimes upto 10s in case of very large data eg. 2 million records). What takes time is getting all that data from db and then returning it to the frontend which anyway is not a good practice (the size of data would be over 100mb in most cases). Yes pagination is definitely required but that makes the Include all and Clear All features that I mentioned a little tough to implement. – Hardik Aswal Aug 12 '21 at 08:32
  • @HardikAswal so take the pagination approach with select all also. In the case he click `select all` you display a notice `selected 10 items out 10,000 result` -> that way we avoid stress on network, show correct / honest infomation and fulfil user's expectation to some extent – Phạm Huy Phát Aug 12 '21 at 08:41
  • @PhạmHuyPhát Yes that's not really a big task. But the use case as of now is to select each and every resource which contains the search string that has been set by the user. Again, for eg, search string 'ad' will ensure that Adam, Adrian,....Brad etc all are selected. – Hardik Aswal Aug 12 '21 at 09:00
  • 1
    @HardikAswal Sounds like you need specific mutations for "Include All" and "Clear All" that don't take a list of ids that was previously queried, but instead take the search string as input. – Bergi Aug 12 '21 at 09:41
  • @Bergi Yes that's what I am trying to implement. As of now I have come up with a solution wherein on the GraphQL side, I'll have pagination with a limit of 100 and which would fetch me the closest 100 records to the search string that the user has provided (if any). On the react side, just like any normal dropdown, the users will be able to add/remove using checkboxes & move across pages & provide search string. If include all is clicked, I'll have another array which would contain the search string on which include all was clicked, so in sql I can use that string with LIKE keyword. – Hardik Aswal Aug 12 '21 at 10:07
  • @Bergi I'd also need to show the options as selected across all pages which can be done by just checking whether the option is there in selected resources or whether any of the search strings in the state array we took are 'LIKE' it. But still the clear all problem remains unsolved and also the code for this solution could become a bit messy. – Hardik Aswal Aug 12 '21 at 10:09
  • @HardikAswal I would expect that all the resources in your state are LIKE the current search term, so when clicking "Include All" you'd just mark them all as selected. – Bergi Aug 12 '21 at 10:12
  • 1
    @HardikAswal I don't see how "Clear All" is any different from "Select All", except that it puts the inverse value? If you thing your code is messy and need help improving it, please [edit] your post to include it. – Bergi Aug 12 '21 at 10:14
  • @Bergi All the resources in my state can't be LIKE the current search string. Since the user may select a few resources which contain say 'cat' in them and then a few resources which contain 'dog' in them and then search for 'fish' and hit include all which should select all the resources which contain 'fish', even if they are not being rendered on the UI right now. – Hardik Aswal Aug 12 '21 at 10:27
  • @Bergi On the Clear All part, yes I figured out that Clear all would just Clear each and every selected resource and selected search string from the state, giving me nothing but the first 100 resources itself, without any filters. – Hardik Aswal Aug 12 '21 at 10:29
  • 1
    @HardikAswal Ah, I had assumed you would keep only the search results for the current search term in your state, not all results ever fetched. Yes, if you do that, when "Include/Clear All" is clicked you either a) need to clear your state completely b) update your state by evaluating the LIKE on the client as well or c) update your state by returning all the resource ids whose selection changed as the mutation result (possibly too large). – Bergi Aug 12 '21 at 10:34
  • @Bergi I think the first option makes the most sense when it comes to clear all. Just clear out every single thing from both the states i.e. selected resources and selected search strings. There would also be a third state though, which would hold any resource id which would be excluded after include all. For eg. say there are 1000 resource ids LIKE the current string 'cat', the user hits include all so all 1000 of them would now be included, but say there's a particular resource id which the user knows to be a 'testing' resource id and wants to exclude it, so it can be stored in diff state. – Hardik Aswal Aug 12 '21 at 11:05

0 Answers0