Mandy made a website for Women who, like her, really want to have proper pockets in their clothes. I remember her coming up with the idea, and then a few years later it turned out it was a pretty common idea.

Caroline Criado-Perez had also had the idea, and someone we met a Sheffield Digital event we then had coffee with had the idea too. So it seemed like a popular idea. Mandy got to work and produced what I think is a tremendous piece of work, a site that is great for Dresses with Pockets and Leggings with Pockets.

In an ideal world we would have all the clothes with pockets on there, and the pricing would all be up to the minute. In effect that’s not how it works. The only way to get the data is by signing up as an affiliate to a retailer, and then using their product feeds.

She could try to scrape the websites, but there’s alot of that going on a the moment and there are products being created, by the likes of Cloudflare to stop that from happening without some kind of fee.

The hope is that the affiliate commissions will earn enough in time to pay for the site, but at the moment it’s not popular enough.

As we run together a Sheffield Web Development Agency we’ve been able to use the site to try out some new technologies. Product search is one such that we needed to solve, and we had to do it cheaply. I remember how amazed I was at the ability of Algolia to return search results to the front end in 10ms. They must have servers everywhere, and their code must be great. It was wonderful when we were using it for Online4Baby a few years ago, but since then it went from $60 per month to more like $6000 for their usage, and for the same thing. They clearly have done really well for themselves, but they also priced Mandy’s Pockets for Women site out.

So I looked at text search and found Laravel Scout, which has a postgresql plugin, and that looked perfect. Because Scout is designed to do text search, but not originally on postgresql, so I thought we would be able to use this plugin and put the text search criteria into a single SQL query, meaning we could add on all the filter information. But it wasn’t to be. Scout queries didn’t want to mix with normal Eloquent queries, but I found I could just take the SQL and do something raw.

Actually we just needed to add some functions to the Products model that looked like this:

    public function scopeSearchQuery($query, $search)
    {
        $query->Live();
        if (!$search) {
            return $query;
        }
        $query->whereRaw('searchable @@ websearch_to_tsquery(\'english\', ?)', [$search])
            ->orderByRaw('ts_rank_cd(\'{0.1,0.2,0.4,1}\', searchable, websearch_to_tsquery(\'english\', ?),32) DESC', [$search]);
    }
    public function scopeSearchQueryOrderBy($query, $search)
    {
        $query->Live();
        if (!$search) {
            return $query;
        }
        $query->orderByRaw('ts_rank_cd(\'{0.1,0.2,0.4,1}\', searchable, websearch_to_tsquery(\'english\', ?),32) DESC', [$search]);
    }

We still needed the plugin and scout to setup the columns for the search, but this added the ability to combine text search with filter search and all the other normal stuff you do with Eloquent.