How to implement search using SQL query

How to implement search using SQL query

  

Hi,

I need to implement search using sql query how can i pass the value of inputfield text and date value  to the  SQL query.

Thanks,

Ronan T.

Hi Ronan,

SQL queries have Input Parameters, just like Actions. This is pretty basic though, I would advice you to (re)visit the documentation, and if you haven't done already, the online training courses.

Like below snap.

Kilian Hekhuis wrote:

Hi Ronan,

SQL queries have Input Parameters, just like Actions. This is pretty basic though, I would advice you to (re)visit the documentation, and if you haven't done already, the online training courses.

Hi Kilian Hekhuis,

    Can we able to implement universal search on paginated table values.

Thanks,

Ronan T


Hi Ronan,

This is typically handled by implementing one or more filters, to you only display what has been searched for.

Hi Kilian Hekhuis,

Actually we are not using aggregates to fetch the values we are writing sql query to fetch set of records and onclick of button to fetch the next set of data but on applying of filter it is searching only the fetched values in the table 

here is my query



SELECT {Movies}.[Id],{Movies}.[Title] from {Movies} where {Movies}.[Id] between @In1 and @In2  and
{Movies}.[Title] like '%'+@In3+'%' and {Movies}.[Title] like '%'+@In4+'%'

Thanks,

Ronan T.

Hi Ronan,

First, use Aggregates where ever you can. Such a simple query as the one above has no use being written in SQL, other than as an excuse for keeping your old habits.

Secondly, I don't see why you would want to query all data, only to search within that dataset afterwards. Do you have a very slow database server, or a very slow network connection or the like that you feel you umust do this?
Filtering in the database is almost always preferable, as application server-side filtering is typically much slower.

Kilian Hekhuis wrote:

Hi Ronan,

First, use Aggregates where ever you can. Such a simple query as the one above has no use being written in SQL, other than as an excuse for keeping your old habits.

Secondly, I don't see why you would want to query all data, only to search within that dataset afterwards. Do you have a very slow database server, or a very slow network connection or the like that you feel you umust do this?
Filtering in the database is almost always preferable, as application server-side filtering is typically much slower.

Hi Kilian Hekhuis,

  1. why we not using aggregate means we need to fetch set of data for pagination ,not all the records from the db at once.
  2. so in the preparation we are fetching records using sql queries.
  3. we are able to search the records based on criteria for the particular page but not universal search.


Hi Ronan,

That question has been answered in the other topic you created. The OutSystems Platform already makes sure it doesn't fetch too many records when paginating. You should use the Platform's standard way of dealing with that. _Always_ use Aggregates instead of SQL queries of possible (i.e. if it's possible with an Aggregate, use an Aggregate), _especially_ when you're a novice OutSystems user.

Also, avoid premature optimization. Like someone mentioned in the other topic, it seems you think there's a problem that you haven't even encountered yet.

Thirdly, I'm not sure what you mean with "universal search". Showing data means filtering. You don't want to recreate Google.