12 Replies Latest reply on Dec 4, 2012 5:42 AM by user18850

    Search Operators

    user18850

      - Suppose I have an application that searches for cars.

      - I want to find all of the cars that are Blue or Green and are made by Honda or Toyota

       

      In SQL I would write:

      (Blue OR Green) AND (Honda OR Toyota) AND (Other Stuff)

       

      In Filemaker I seem to need to do this:

      (Blue AND Honda AND Other Stuff) OR

      (Green AND Honda AND Other Stuff) OR

      (Blue AND Toyota AND Other Stuff) OR

      (Green AND Toyota AND Other Stuff)

       

      You would use a Compound Search to achieve this.

       

      The issue is the more ORs in the search, the more queries will be produced.

      1 would produce 2 searches.

      2 would produce 4 searches.

      3 would produce 8 searches.

      10 would produce 1024 searches.

      n would produce 2 to the power of n (2^n) searches (exponential = ahh!).

       

      This is only if each OR has 2 choices.

       

      So my question is: Can I write one statement that does this (like in SQL) instead of creating thousands of queries?

       

      Thanks

        • 1. Re: Search Operators
          beverly

          Take a look at the Extend and Constrain found set. These allow you to sub-query an original query.

          http://www.filemaker.com/12help/html/find_sort.5.18.html#1028651

          http://www.filemaker.com/12help/html/find_sort.5.17.html#1028608

           

          Also, if you have relationship that "finds" some of these, you can filter a portal with your AND/OR to reduce the set even more. Put this into a global field (or variable), so that you can change the query "manually", then use the global or variable in the filter.

          http://www.filemaker.com/12help/html/relational.11.21.html#1033103

           

          Beverly

          • 2. Re: Search Operators
            user18850

            Thanks for the reply.

             

            I should have mentioned that this is with reguards to searches using the PHP API.

            • 3. Re: Search Operators
              beverly

              I should have mentioned that this is with reguards to searches using the PHP API.

               

              Yes, and a scripted method may still work with PHP API!

               

              What are you using for "selection" to submit the finds? Do you have a form with checkboxes, radio buttons, drop-down lists etc.?

              Please be more specific.

              Beverly

              • 4. Re: Search Operators
                user18850

                The search I am building is an advanced search, it has multiple dropdowns, checkboxes, and radio buttons. All of the post information is mapped to a search object, and I use that object to build a search using the PHP API.

                 

                I know the PHP API has the following helpful methods:

                   -newFindCommand

                   -addFindCriterion

                   -newCompoundFindCommand

                   -add [add compound find]

                 

                Thes do not seem to allow for this though:

                     (Blue OR Green)  AND (Honda OR Toyota) AND (Other Stuff)

                 

                Are there methods that would allow me to do this?

                 

                Thanks

                • 5. Re: Search Operators
                  user18850

                  This person seems to have had the same issue. There is a 'solution' stated, but it does not get around the fact that you need to create thousands of cases.

                   

                  https://fmdev.filemaker.com/message/3471#3471

                  • 6. Re: Search Operators
                    DavidJondreau

                    You could build the query using a For or a While Loop. You''ll still have 1000 search criteria, but it's a lot easier to build. Don't know if it murders your performance or not.

                     

                    You could also call a FileMaker script to run an ExecuteSQL() command, and then figure out how to display the data, or to do another PHP API call using the IDs you got from the ExSQL.

                    • 7. Re: Search Operators
                      user18850

                      What I did was take the post array and duplicate it on the OR keys. So if you have color = Blue, Green then you create two exact arrays, but one has Blue and one has Green. Then you duplicate those arrays on the next key... That at least removes some crazy nested loops.

                       

                      Again that still does not get rid of creating thousands of searches.

                       

                      I will look into that ExecuteSQL command, thanks.

                       

                      Kind Regards,

                      Brian

                      • 8. Re: Search Operators
                        mbraendle

                        You don't need to create thousands of cases. You just need to create the disjunctive normal form of your query (Edit: as you did in your question).

                         

                        https://fmdev.filemaker.com/message/58749

                         

                        See also related discussion in the Techtalk archive. Search by "PHP newCompoundFindCommand()".

                        • 9. Re: Search Operators
                          user18850

                          Thanks for the reply. Disjunctive normal form will create that exponential 2^n growth.

                           

                          http://en.wikipedia.org/wiki/Disjunctive_normal_form

                          • 10. Re: Search Operators
                            mbraendle

                            For me the question is rather: Is this type of query in your system expected, daily user behaviour?

                             

                            As I know from statistical studies on library and other information systems (I have done myself one on a library system, by analyzing about 150'000 queries, and another on a chemical structure and reaction database with about 100'000 stored queries):

                             

                            >90% of the users use the standard single-line, "Google-like" queries

                            <5% use advanced queries (e.g. a combination of fields with AND, OR, NOT)

                            <5% use enhanced queries such as subject classification, thesauri, etc.

                             

                            Of this minory (5-10%), you can guess that most of them are information specialists that work with the system or produce data in the system.

                             

                            According to my experience, 85% of the users use 1-3 words in the query. Chances that something is being found go down drastically beyond 4 words in the query, except you provide something like Google suggest (typeahead), which helps to reduce typos. Best measures to improve the query (and hence success) before it is sent to FileMaker is to filter stopwords and remove irrelevant special characters such as . , : and others.

                             

                            I don't want to tell you that you shouldn't implement complex Boolean querying with FileMaker and the PHP API - there might indeed be good reasons for you to do so. For our systems, I had also thought about implementing  that and came to the conclusion that it was not worth the effort, especially because it's not that easy to create the DNF if you have multiple, convoluted requests. If I had to do, I would expose the data to a good internal search engine such as Apache Lucene - which has the capability to identify word stems and offers all the logic and in addition proximity operators for doing advanced searches.

                            • 11. Re: Search Operators
                              mbraendle

                              You could also use JDBC which allows the type of SQL queries (and many more complicated SQL statements) you have in mind.

                               

                              Have a look at the presentation given by Bernhard Schulz at the recent Austrian-German-Swiss FileMaker Conference 2012:

                               

                              http://de.slideshare.net/fmkonferenz/fmk2012-filemaker-und-java-und-es-offnen-sich-neue-welten-von-bernhard-schulz

                              • 12. Re: Search Operators
                                user18850

                                Thanks, I will take a look