Collection orderBy url ignore protocol and www subdomain

Hi @nilsringersma

Maybe you could try doing a raw query using REGEXP_REPLACE or REGEXP_SUBSTR with an alias, and then ordering by that alias.

ref: https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace

Hope this helps!

@nilsringersma try this sample query directly on your database and see if it's what you may be needing:

SELECT REGEXP_REPLACE('https://www.laracasts.com','http://|https://|www.','',1,0,'c') as URL order by URL;

In your case you can replace 'https://www.laracasts.com' with the url field of your table.

Here you have a working example: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=92920e3ec2af40fc82283fed80d8e420

Let me know if this worked for you!

to get the host name, you can try this:

return parse_url(request()->root())['host']; 

https://www.php.net/manual/en/function.parse-url.php

but I'm not sure how to remove the subdomain as it is not easy, the subdomain can be www2. or without the www, or multiple level subdomains, and the root domain can be .com, .my, .net.my...

Hi @siangboon and @marianomoreyra . Thanks for your advise! Both answers resulted in a fix. I've improved my app by storing the 'nicename' for the URL in a new property for my model. This way I don't have to replace it on runtime for each request.

Thanks for the help :)!