I have a query that uses "AS" and "SUM". I am unable to find any applications of this in the Phoenix documentation, so I am starting to wonder if I should just use raw SQL queries. I am new to Phoenix, so perhaps I may have missed something.
My query is:
SELECT SUM(`http` = 1) AS http_prox,
SUM(`socks` = 1) AS socks_prox,
SUM(`is_ssl` = 1) AS ssl_prox,
SUM(`sneakers` = 1) AS sneak_prox,
SUM(`shopify` = 1) AS shopify_prox,
SUM(`google` = 1) AS google_prox,
SUM(`ebay` = 1) AS ebay_prox,
SUM(`strawpoll` = 1) AS strawpoll_prox
FROM `proxies`
I want to know if it is bad practice in Phoenix to execute text SQL queries (instead of making one using Ecto), and if it is, how do I tackle this problem? I could perhaps send multiple queries to get the sum of each, but that isn't efficient.
I would not say it is a bad practice. However, I believe more people that are familiar with Phoenix have at least some experience with Ecto. I believe using Ecto would allow other developers to start contributing to your project faster.
With that said, if you really don't want to use Ecto, you can just use the Elixir driver for your given database. You also have the option of using a fragment for the portion of the query you are unsure, or unable, to write within Ecto itself. There is also the query function where you can run a text query against your database.
For what it's worth, you should be able to write your query as:
MyApp.Repo.one(from p in MyApp.Proxies,
select: %{http_prox: sum(p.http == 1)
socks_prox: sum(p.socks == 1),
ssl_prox: sum(p.is_ssl == 1),
sneak_prox: sum(p.sneakers == 1),
shopify_prox: sum(p.shopify == 1),
google_prox: sum(p.google == 1),
ebay_prox: sum(p.ebay == 1),
strawpoll_prox: sum(p.strawpoll == 1)
})
Keep in mind this is untested because Postgres does not allow for a boolean in the sum function and I do not know what DB you are using.