using mysql stored-function with select query

Question

let me explain my purpose first, i have an vehicle booking application where, visitor will add start date and end date of his journey, in the database there is list of drivers with there availability (available_from_date and available_to_date) which is kind of duration during which they are operating, there is an field for exclude_dates for some specific dates when they are not working. the application needs to find a list of vehicles which are available during the journey dates entered by the user. for example user enters he want to go from place A to B during 13th sept, 2014 to 17th sept, 2014 then database needs to return a list of taxi which are available during this period and must not have any exclude date within this period.

Now i have stored the exclude_dates in comma separated format in table (i could have created a separate table but then it would take much more time for a query to execute) I was trying to create a mysql function which would be called within the actual search query and would return true if there is some there is some excluded date present within the duration and false if not.

these are the queries that i have written

SELECT id, exclude_dates  
FROM `taxi_route` 
WHERE  status = 1 
  AND `to_city` = 'Surat' 
  AND `from_city` = 'Ahmedabad' 
  AND `trip_type` = 2 
  AND `available_from_date` <= '2014-09-13' 
  AND available_to_date >= '2014-09-17' 
  AND STR_TO_DATE((SELECT `split`(exclude_dates, ',', 1)),'%d-%m-%Y') 
      NOT BETWEEN STR_TO_DATE('13-09-2014','%d-%m-%Y') 
              AND STR_TO_DATE('17-09-2014','%d-%m-%Y')

Split is a function i have created in mysql to separate the dates present in comma format

DELIMITER $$

CREATE FUNCTION split( str VARCHAR(500), delchar VARCHAR(2), x INT )
RETURNS VARCHAR(500)
BEGIN
RETURN SUBSTR(SUBSTRING_INDEX(str, delchar, x),
LENGTH(SUBSTRING_INDEX(str, delchar, x-1))+IF(x > 1, 2, 1));
END$$

DELIMITER ;

this works fine as far as i pass 1 in split(exclude_dates, ',', 1) , but if the exclude_dates have more then one date then this will not work can someone please suggest or guide, how this can be accomplished. snapshot of database is here https://i.sstatic.net/LeT8I.png

hi @Kermit, thanks for commenting but don't we need to convert the string given by user to a valid date format also the exclude_dates is a varchar and when we extract a date out of it would be a string which again needs to be converted to date format? — Sanket Bhatt
– Sanket Bhatt, Commented Sep 12, 2014 at 18:03

oardic · Accepted Answer · 2014-09-12 19:25:28Z

1

Your query is most likely going to take more time to execute than defining a separate table for exclusion dates. It's not a good practice using comma separated list inside a column for searching purposes, this is against normalization rules.

You should define your tables separately, (e.g. taxi, taxi_route, taxi_route_exclusion, route_exclusion) and later add necessary indexes to make your searches more efficient.

Example:

taxi
---------
id
country
***
***
***

taxi_route
-------------------
id
taxi_id
available_from_date
available_to_date
from_city
to_city

route_exclusion
---------------
id
taxi_id
exclusion_date

And also add a relation table between taxi_route and route_exclusion tables to represent many-to-many relationship. Later define foreign keys on taxi_route_route_exclusion table to point taxi_route and route_exclusion tables.

taxi_route_route_exclusion
--------------------------
taxi_route_id
route_eclusion_id

Define foreign keys like:

taxi_route.taxi_id -> taxi.id
taxi_route_route_exclusion.taxi_route_id -> taxi_route.id
taxi_route_route_exclusion.route_exclusion_id -> route_exclusion.id

Define indexes like:

taxi: IX1 (status, trip_type)
taxi_route: IX1(to_city, from_city, available_from_date, available_to_date)

Your final query should look like this:

SELECT tr.id, re.exclusion_date
FROM `taxi_route` tr JOIN `taxi_route_route_exclusion` trre
  ON tr.id = trre.taxi_route_id
JOIN `route_exclusion` re
  ON re.id = trre.route_exclusion_id
JOIN `taxi` t
  ON t.id = tr.id
WHERE
  t.status = 1
  AND t.trip_type = 2 
  AND tr.to_city = 'Surat' 
  AND tr.from_city = 'Ahmedabad' 
  AND tr.available_from_date <= '2014-09-13' 
  AND tr.available_to_date >= '2014-09-17'

answered Sep 12, 2014 at 19:25

oardic

2121 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sanket Bhatt Over a year ago

hello @oardic, thanks for the explanation, but i think stored procedure or function would perform much faster then joins on mysql, we might have thousands of such records and then many exclude_dates in each of them using a join would consume lot of time and memory, rather a stored procedure would just return result in form of yes or no, using which we can have a normal select query, which only checks if there is some excluded date in between the journey or not. [please correct me if i am wrong]

oardic Over a year ago

Using functions will result in calling them for each record returned. Depending on your query it might return the results much slower than sql joins which doesn't seem to be a good approach in your case. Besides, joins are much clearer than functions; if you are able to write your query in a single statement rather than using functions, your query will be more readable. Using stored procedures is something different. You can define your stored procedure and write the above query inside the definition. For performance issues consider using indexes.

Sanket Bhatt Over a year ago

Hello @oardic, i tried to implement your suggestion by adding a new transaction table of taxi_route_excluded_dates, we can not have a different route_exclusion table since the excluded dates are added in respect to the route itself. say for an example driver D operates from place A to place B from 1st of every month to 10 then he operates from place B to C from 11 to 20, then X to Y from 21 to 30, BUT he is not working on 5th, 15th and 25th dates of month, and thus the system should not show his taxi, if anyone plans journey on or during these excluded date/s.

Sanket Bhatt Over a year ago

i have created a new table called taxi_route_excluded_dates with id,taxi_route_id and excluded_date as three fields and i am running below sql, however it seems that it doesn't work for me to check if there are some excluded dates between the journey dates give by user

Sanket Bhatt Over a year ago

here is the sql SELECT tr.id, trre.exclude_date FROM taxi_route tr JOIN taxi_route_excluded_dates trre ON tr.id = trre.taxi_route_id JOIN registered_taxis t ON t.id = tr.id WHERE t.status = 1 AND tr.trip_type = 2 AND tr.to_city = 'Surat' AND tr.from_city = 'Ahmedabad' AND tr.available_from_date <= '2014-09-13' AND tr.available_to_date >= '2014-09-17'

|

Collectives™ on Stack Overflow

using mysql stored-function with select query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related