FIND_IN_SET. A handy little MySQL function

MariaDB MySQL

At AtYourService.com.cy we send a lot of emails. Especially when you compare it to the Cyprus market we send even more. Advocates of the Lean method we measure and report EVERYTHING. We even monitor some data real time. Reporting of a live table is a no go when you are running a real time application. So a lot of reports contain a lot of GROUP_CONCAT fields. When it comes to combinig these reprots with a semi hot table its kind of a mess to join over a field containing comma separated keys from another table.

SELECT * FROM 
table1,report_table2 
WHERE 
date BETWEEN 2015-01-01 AND NOW() 
AND table1.id in (x1,x2,x3)

But x1,x2,x3 is actually a text field in report_table2.

WHERE id in (table.field)

throws an error (well maybe not if the values are integers and MySql CASTs them to the first scalar value and if you are lucky the expression will evaluate to true if you hit the first one)

The solution?

SELECT * FROM 
table1,report_table2 
WHERE 
date BETWEEN 2015-01-01 AND NOW() 
AND FIND_IN_SET(table1.id,report_table2.field)

Maybe I should have known this but I didnt. It blew my mind and I thought I had to share.

Share your favourite MySQL function in the comments below

How I learned how to work

How to work efficiently

2014! My first full calendar year in a startup!

The best thing about being in a startup is… Well you can read on or just google it.  

One thing stands out besides working with a great team. A team constantly delivering at full pace and producing what traditional 50+ employee companies with just a fraction of the resources.

Learning

One of our core values as a team but also a personal value of mine.

Its hard to keep up with everything but I am not talking about tech or startup news. I am talking about constantly improving the way you work, by looking at the medium/long term benefits.

One might say this is very vague but I boiled it down to 3 main things to pursuit in your everyday working schedule.

These are not strict to software development and I will do my best to be generic.

Efficiency

Use task management tools like Asana, and avoid communicating through email. Use a closed facebook group or an enterprise social network like Yammer.

There are thousands of tools and services out there to help you organise and automate your workflow.

My target for 2015 is

Automate everything you do for the third time

This can be applied to new feature development or just simple daily mundane repetitive tasks. Use macros in the software you use. If you don’t know how ask someone to do it for you.

Write shell scripts / batch files for everything that you do regularly.

 

Predictability

Have a timesheet of your countable (and repetitive) tasks. For example it takes me a  full hour to make a report table from database to ui.

Time your tasks and the bookkeeping will help you reduce planning fallacy. You will also have a better sense of when you are dragging a task too far along.

Whenever you can work for the future do it. Aiming for the long run, even if you are in the MVP stage, is good practise. You may not use it for your current project but there will always be a next one:)

Recovery

Have your rituals and your procedures on the top of your head. Having a crisis plan is not the worst choice. Don’t obsess about something happening,  Only obsess about how great your recovery plan is:) Note down how you solve problems and keep it handy for the next time.

Recover your brain to prevent burnouts. I work for 50 minutes and have a break for 10 minutes. Get some days off. You’ll make a hell of a comeback.

 

Research

Yeah I know I said 3 but keeping up with all of the above needs research and good old fashion studying. Reading the latest blog posts and tweets is not enough.

Get your gloves on and try all the new methods and tools you read about. Most of the time we develop habits which actually stop us from improving our workflow. The well known way is the fastest way when the clock is ticking. Try something new and bold. What’s the worst that can happen? You learn if a way is better or worse.

Anw enough mumbling. Pull your sleeves up and start playing:)

Submitting forms using iFrames on iOS devices

Silent but deadly

Working on AtYourService.com.cy I have to submit a lot of forms using iframes since almost all forms have file attachments and a lot of feedback from our application before completing the process. A weird bug was eating my brain when all forms that returned html messages in the response object suddenly stopped working on iOS devices. I used a jQuery extension which submitted the form in an iframe and I returned json encoded data from the server. The script simply wrapped the response in a <pre> tag and fetched the contents to decode and pass them back on the client side handler. All works fine unless you have some numbers in an html json property and mr Apple decides to convert them to anchor links with a tel: prefix in the href to call phones directly. A handy little feature when it works correctly. But since the double slashes are not escaped you are left with an invalid json syntax. The quick solution is to just return the data and construct the html with javascript. it is indeed more efficient but when you are developing an mvp or just testing an experiment you simply dont’t have or want to waste time on making two templates.