Kyan blog

Custom Selects in MySQL

2016-04-12T12:00:00Z

We had a situation with a project recently where we were trying to display tens of thousands of rows of data. It needed to be displayed in the browser, it needed to be nicely formatted and it needed to be fast. Unfortunately, that final requirement was causing problems.

The Why

The dates had to be readable, first and last names were to be concatenated to one cell and boolean values should return ‘True’ or ‘Approved’ rather than 1.

Using Ruby/Rails for this was slow. We were calling the database multiple times from within the view to query the models, and doing that per row was taking us past the 30 second timeout limit.

So we decided to populate all of the table’s data in the controller, doing a single call to the database, creating an array of arrays from this and just outputting this row by row, cell by cell in the view.

And it worked! Times were brought down from over 30 seconds per report to sub-second renders.

The How

In Ruby, we built a MySQL query string up, passed it to MySQL through an ActiveRecord::Base connection to get back a Mysql2::Result, and just looped through this in the view.

sql_string = %Q( SELECT DISTINCT

~interesting selects here~

FROM xxxx

WHERE yyyy

GROUP BY zzzz)

@results = ActiveRecord::Base.connection.execute(sql_string)

The interesting part of this was actually finding ways to format the data into exactly what you wanted, within MySQL.

Concatenating fields

CONCAT | CONCAT_WS

SELECT concat(“Frank“, “ “, “Smith“) AS fullname

-> 'Frank Smith'

SELECT concat(u.first_name, “ “, u.second_name) AS fullname

-> 'Bob Smith'

SELECT concat(“Peter“, NULL, u.second_name) AS fullname

-> NULL

SELECT concat(12) AS fullname

-> '12'

SELECT concat_ws(“ “, u.first_name, u.second_name) AS fullname

-> 'Bob Smith'

SELECT concat_ws(NULL, u.first_name, u.second_name) AS fullname

-> NULL

SELECT concat_ws(“ “, u.first_name, NULL, u.second_name) AS fullname

-> 'Bob Smith'

This returns the string result of the concatenated arguments. However, if any of the arguments are NULL, the entire result will be NULL.

Concat_ws stands for ‘Concatenate With Separator’, where the first argument will be used as the separator for the rest of the arguments. Unlike ‘concat’, ‘concat_ws’ will only return NULL if the first argument is NULL, any following NULL arguments are simply ignored.

GROUP_CONCAT

SELECT group_concat(DISTINCT concat(f.name, ‘ (‘, f.type ‘ )’)

ORDER BY f.name DESC, SEPARATOR ', ‘) AS food_groups

-> 'Apple (fruit), Carrot (vegetable), Orange (fruit)'

SELECT group_concat(DISTINCT concat(fr.first_name, ' ', fr.second_name) SEPARATOR ', ') AS friends

-> 'John Doe, Jane Doe'

SELECT group_concat(DISTINCT concat(fr.first_name, ' ', fr.second_name) SEPARATOR '----') AS friends

-> 'John Doe----Jane Doe'

SELECT group_concat(DISTINCT fr.first_name) AS friends

-> 'John,Jane'

A group_concat lets you grab the data from a JOIN table and display the results in a single cell, separated however you want. Calling DISTINCT tells the database to only return unique items, and passing a SEPARATOR formats the result into a more readable state, otherwise it will return a comma separated list but with no space between the results.

Coalescing fields

COALESCE | IFNULL

SELECT COALESCE(NULL, 1);

-> 1

SELECT COALESCE(NULL, NULL, NULL);

-> NULL

SELECT COALESCE(u.updated_at, u.created_at, 0)

-> 2013-03-17 11:30:57

SELECT IFNULL(u.num_friends, 0)

-> 12

SELECT IFNULL(u.first_name, 'No First Name Set')

-> 'No First Name Set'

Coalesce returns the first non-null value found in a list, or null if no non-null value is found. I found this useful in the latter case in order to ensure I was always dealing with an integer value. IFNULL is similar to coalesce but will only take one value, checking if it’s null and, if so, falling back to the given default value.

Date/Time fields

DATE_FORMAT | DAYNAME | MONTHNAME

SELECT DATE_FORMAT(u.sign_in_date, “%d %b %Y %H:%i“)

-> '14 Sep 2012 11:25'

SELECT LEFT(DAYNAME(u.sign_in_date), 2)

-> 'Fr'

SELECT MONTHNAME(u.sign_in_date)

-> 'September'

Bear in mind this uses slightly different syntax to ruby strftime

Notable differences:

Hour, padded digits (01 through 12)

%I : Ruby
%h : MySQL

Weekday name (Sunday through Saturday)

%A : Ruby
%W : MySQL

Month name (January through December)

%B : Ruby
%M : MySQL

Boolean fields

IF |CASE

SELECT CASE
 WHEN t.approved = TRUE THEN "Approved"
 WHEN t.approved = FALSE THEN "Declined"
 ELSE “Pending"
END AS approved

-> 'Pending'

SELECT IF(t.completed_at IS NOT NULL, ‘Complete', 'Incomplete')

-> 'Complete'

IF / CASE statements are great for formatting boolean values into readable data. While IF-ELSE statements do exist in MySQL, I personally find CASE statements more readable when you’re dealing with more than 2 potential outputs.

In Conclusion

I’ve found that being able to wrangle raw SQL data into a human-readable format has been useful in a variety of ways, from increasing report output speeds by looping through pre-formatted data to simply being able to understand your database query results at a glance.

Keeping in the know!

2016-04-05T10:10:27Z

Everyday the whole team consumes and shares information from a wide variety of sources, be it blogs, articles, stories or inspiration galleries. We’ve plucked out some of our favourites.

1. Ruby weekly

“The Ruby community is probably best served by the weekly newsletter that comes from rubyweekly.com” – Paul Sturgess

2. Medium

“The place I read most long form content, as more individuals from twitter are putting their greater than 140 character thoughts here.” – Rob Edwards

3. Hacker News

“The latest in user submitted tech news, commentary is usually kept clean and useful” – Phil Balchin

4. Its Nice That

5. Creative Review

6. Colossal

“These 3 keep me inspired throughout the day, mostly on a design/art focus” – Rob Edwards

7. DesignTAXI

“Interesting commentary for many different areas of the design network” – Piers Palmer

8. A list Apart

“If you work on the web and you don’t know this by now, shame on you!” – Gavin Shinfield

9. Codrops

10. CSS-Tricks

“The most interesting techniques for front-end development usually turn up on these sites.” – Rob Edwards

World book day... our top picks!

2016-03-03T14:15:00Z

It’s World Book Day today, March 3rd (#WorldBookDay) — be it Kindle, iPhone or even good ol’ fashioned print, we love to read, and not just Swiss design books and geeky O’Reilly tomes, we also enjoy a good novel, graphic or otherwise! We’ve put together a small personal selection of our top picks and why we love them so much. Happy reading!

Ridley Walker by Russel Hoban

“An underrated gem, set in a post-apocalyptic South East England and narrated in it’s own pidgeon argot this book unfolds like a puzzle.”

The Hobbit by J RR Tolkien

“The quintessential fantasy tale. Read it when I was 9 and have read it again every few years since.”

When You Are Engulfed in Flames by David Sedaris

“The New Yorker humorists very amusing collection of short essays, which started my audiobook obsession. Immediately read (listened) to his complete bibliography.”

The Marriage of Cadmus and Harmony by Roberto Calasso

“Every time I read it, it blows my mind. Mystic, mythical & mischievous.”

The Road by Cormac McCarthy

“More Apoca-lit; Moving, savage and redemptive.”

Carol (The Price of Salt) by Patricia Highsmith

“Beautiful story following a budding romance and the suffering it causes those in and around the relationship, the growth of the main characters through the story and the amazing setting (1940s Manhattan) made it one of my favourite recent-reads.”

The Grown-up by Gillian Flynn

“Very short horror story by the author of Gone Girl + others, a fun read with an ending that leaves you guessing.”

All the Light We Cannot See by Anthony Doerr

“Pulitzer prize winning novel exploring the lives of two very different children before & during the Second World War. Spellbinding prose & profound themes stretch across time and borders.”

The Great Gatsby by F. Scott Fitzgerald

“Possibly the finest novel of its generation. Haunting, melancholic, funny, lyrical and unforgettable.”

Fictions by Jorge Luis Borges

“A collection of philosophical short stories which traverse humour, ethics and metaphysics whilst remaining compelling and joyous”

If On A Winter’s Night A Traveller by Italo Calvino

“Clever meta-fictional shenanigans exploring the relationship of author and reader. If you are reading this …”

The Killer is Dying by James Sallis

“James Sallis, author of Drive (that cool film with Ryan Gosling in the silver jacket) writes terse sun-drenched crime fiction from an outsider’s first person viewpoint. This was the first book of his I read and plan to read many more. Cool, sparse and evocative, reads more a like Raymond Carver-esque lierary novel rather than genre fiction.”

Kyan's social 2015

2016-01-14T11:36:00Z

We like to make sure we have plenty of fun outside of the coding and design world, so let’s take a look back and re-live some of the awesome things we did in 2015.

Web Meet Guildford

Still going strong after 5 years, we’ve continued to enjoy meeting up with some familiar and new faces who have joined us for Web Meet Guildford (WMG). It’s always a nice opportunity for us and everyone involved to unite and discuss our favourite topic, whilst sipping on our favourite beers!

Check out the WMG website for the latest updates and #WMG on Twitter.

Quiz-tastic

Everybody loves a quiz…well, we do! Every so often, someone will step up to the challenge of ‘Quiz Master’. This year’s quizzes were varied and full of brain tickling material. We could impress you with our new found knowledge on pylons …you know, those tall ugly metal structures along the roadside, but we’ll save that for another time.

Cinema trips

It seems if it’s not a superhero, it’s a spaceman!

We love a blockbuster, we even have our own movie chat room to share all the amazing and not so amazing films we watch. For us, a cinema trip will only happen if we’re pretty sure we’ll like it! Our top choices this year were Avengers: Age of Ultron, The Martian and (of course) Star Wars: The Force Awakens. I think that’s 8 out of 10’s all around…

Topgolf

Fore! We had tons of fun hitting balls into the great big nowhere! Ok..well…maybe my balls went nowhere but there are some true golfers amongst us who were hitting some perfect 10s!

Picnics

Good company, good food and lovely surroundings… that’s all there really is to say about that :-)

Kyan summer bake off

To celebrate the much loved popular TV series, we decided to roll up our sleeves and get out the rolling pins to create our very own version of The Great British Bake off. To name a few, we had Thom’s ‘1am Sausage Rolls’ (we’re guessing he baked them at 1am), Karen’s creative Jelly Beach cake, Piers’ ‘Love Packets’ and Bobby’s ‘Half a pound’ cookie in the shape of a 50p!

Not a soggy bottom in sight!

Airkix

Everyone wonders what it’s like to fly, so it’s a good job we shook of the nerves and took part in Airkix.
We stepped into our jumpsuits, on went the helmet and goggles, and it was finally time to take to the…wind tunnel?

We were treated to try out Indoor Skydiving, which is a unique type of feeling that you’re flying. You take the scary leap into the noisy blustery wind tunnel and voila, you’re flying! Check out our awesome video of the team flying. Who knows, maybe it’ll be the real deal next time ;-)

Christmas party

You already know we were treated to watch the amazing new Star Wars movie! In fact, you could say our whole Christmas party was themed around the film as our favourite Quiz Master Al, put together a virtual Star Wars themed pub quiz. It’s safe to say we have some true Star Wars egg heads amongst us… Laurent…Phil…

After a few fun filled galactic hours, we sat down and ate to our bellies’ content at Jamie’s Italian, followed by some not so civilised drinks to end the end!

So there we go — I think we injected just the right amount of fun into 2015.

Roll on 2016!

Five questions with our WXG 2015 speakers

2015-06-25T13:17:13Z

It’s just three months until WXG returns for its fourth year, on Friday 25th September.

WXG is a one-day conference dedicated to design, code and creativity, giving you the chance to learn more about your area of expertise or get outside your comfort zone. Above all, it’s the kind of conference we want to attend – friendly, informative and great value.

Here at Kyan we design and develop websites and web apps . We’re big believers in the power of the community to inspire and support each other, so we started WXG to bring people together and celebrate the local creative and digital community.

To get to know our speakers a little better, we’re asking each of them “five questions”, gaining insights into their talks, backgrounds and inspirations in the industry.

We’ll be posting their answers regularly on our WXG Medium blog over the next month, starting with Pete Roome whose WXG talk is ‘The Great Startup Machine’.

Remember to follow us on Twitter @WXG to keep up date with all the latest news.

Early bird tickets are still available via Eventbrite for just £69 ex.VAT.

Hope to see you on September 25th!

The Kyan team

Pattern Libraries - Part 1

2015-05-23T12:00:00Z

Pattern libraries as they’ve come to be known are essentially a set of digital brand guidelines. Print designers have been working from brand guidelines since the beginning of time, so this isn’t really a new concept, we take many of the components of a traditional set of guidelines and expand on those with all of the extra elements that form the structure of a website or application.

The biggest hurdle with this process is time. When we’re working on tight budgets and strict deadlines ‘extras’ like this are often viewed as more of a nice-to-have than an essential addition to a project.

However, once you’ve integrated pattern libraries into your workflow, you soon come to realise that they’re invaluable. Put the time in at the beginning and it’ll pay back ten-fold in the long run.

Some examples

Reference point

In a busy work environment, we often end up jumping between projects through the course of a day. If you create a pattern library as you go along, it serves as a very useful reference point when you come back to a project. I view it as a ‘snapshot’ of the visual language I’m trying to create.

When I’m working on a project, I’ll try to get into the mindset of the client and design in a style that I know will work. When you’re in that zone, it’s easy to make decisions very quickly. However, when you’re forced to break that flow and move to another project, a pattern library can be a way of quickly refreshing your memory when you come back to it.

Everything in one place

This one’s often overlooked, but I find that seeing all your whole user interface together in one place can bring issues to the surface early on. You might not have created a design yet where you have an H4 sitting underneath an H2, but at some point it’s likely to happen. If you’ve never tested these together they might not look quite right. Getting the balance right from the very beginning saves a lot of hassle further down the line.

Drag & drop

When you’re working on big websites, it’s sometimes easy to forget what you’ve already designed. I often find that a design I’ve already worked on for one set of components will apply to another with just minor modifications. Once you’ve started to build up a decent user interface library, it’s often possible to drag and drop multiple patterns to form larger more complicated structures.

Collaboration / Hand over

If there’s several designers working on a project in tandem, then a central design library becomes a necessity. The same goes for hand over, if I’m going on holiday and another designer is looking after my project whilst I’m away, it’s going to save that designer a lot of time getting up to speed if they’ve got one library they skim through before working on anything new.

Consistency

A well designed website relies on consistency. It’s the key to good user experience. Meet the users expectations and you’re well on the way to a successful site.

Admittedly, pattern libraries aren’t by any means the perfect solution but they’re certainly a huge help. The web industry has changed so rapidly that the software we use has struggled to keep up and we’re using tools that were never really designed for what we’re doing.

That said, it is improving – Adobe seem to be gradually adapting Photoshop to address our web related requirements, then there’s applications such as Sketch, that are taking a whole new approach.

Look out for a second part to this post where I’ll talk about the tools we have at our disposal. As designers, should we be simply producing flat designs for our pattern libraries and leaving the front end work to the development team or should we be getting our hands dirty? Also expect a Pattern Library ‘blue print’ that you can use as a reference point when beginning a project.

Samples

Here’s a few samples to demonstrate how we use pattern libraries in our projects at Kyan:

RVM

Nude Designs

LLG

Illumii

Kyan Festive 50 2014

2014-12-24T10:14:00Z

I know I say it every year, but I really, really mean it this time, 2014 has just been a fantastic year for new music.

We had no trouble at all putting together our top 50, well, that’s not strictly true, we quickly got to a longlist of 100 plus tracks and have whittled them down to what we feel is a representative crème de la crème, just for your delectation and delight over the festive season.

Those that didn’t make the cut this year include the irrepressible former Pavement frontman Stephen Malkmus, Temples, I still can’t believe Temples didn’t make a showing, but they just didn’t get enough votes, definitely one of the albums of the year for me. Then there was Tune-Yards, King Gizzard, Sharon Van Etten, Mac Demarco, Childhood, Hookworms, Real Lies, Beck … Lots of really great stuff, maybe we should just publish the longlist!

Anyway, here it is, the top 50 tunes that have been keeping the office bobbing all year, order as voted for by our lovely folks at Kyan. Raise a glass to the memory of the man himself, John Peel — the reason the Festive 50 exists, and enjoy.

Caribou — Can’t Do Without You
Jamie XX — All Under One Roof Raving
Future Islands — Seasons (Waiting On You)
Sohn — Artifice
The War On Drugs — Red Eyes
Todd Terje — Delorean Dynamite
Royal Blood — Come On Over
Jungle — Busy Earnin’
Young Fathers — Get Up
First Aid Kit — My Silver Lining
Ten Walls — Walking With Elephants
Little Dragon — Klapp Klapp
Roman Flügel — Stuffy
The Acid — Animal
FKA Twigs — Two Weeks
SBTRKT — NEW DORP. NEW YORK.
Aphex Twin — minipops 67 [120.2][source Field Mix]
Flying Lotus — Never Catch Me ft Kendrick Lamar
Dan Croll — Wanna Know
I Break Horses — Faith
Goat — Hide From The Sun
Mogwai — Remurdered
Teleman — Cristina
Wild Beasts — Wanderlust
Sophie — Lemonade
Rustie — Raptor
Alvvays — Archie, Marry Me
Wild Beasts — Palace (Foals remix)
Mazes — Salford
Cloud Nothings — I’m Not Part of Me
Real Estate — Crime
Sun Kil Moon — Carissa
Ariel Pink — Put Your Number In My Phone
Alt-J — Every Other Freckle
Thurston Moore — The Best Day
NehruvianDOOM — Darkness (HBU)
Run The Jewels — Blockbuster Night Part 1
Bombay Bicycle Club — Luna
Tony Allen — Tiger’s Skip
2 Bears — Not this time
Woods — Moving to the left
Dark Sky — IYP
Clark — Unfurla
Peaking Lights — Telephone Call
Gardens & Villa — Domino
Alex Banks — Initiate
Jessie Ware — Tough Love
Hudson Mohawke — Chimes
Fujiya & Miyagi — Flaws
Metronomy — Reservoir