Can we select without a table in ecto?

Questions / Help
, ,
#1

I am working with MS SQL server and I am trying to build a complex query with ecto which contains a CTE:

WITH 
	timeslots AS
	(
		SELECT cast('2021-01-09T00:00:00' as smalldatetime) as slot

		UNION ALL
 
		SELECT dateadd(minute , 1, slot)
		FROM timeslots
		WHERE dateadd(minute, 1, slot) < '2021-01-15T00:00:00'
	),

After reading the documentation for CTE with Ecto, I was trying to build the “initial query” but I did not get far.

Indeed, I do not see how I can select a value not coming from a table (e.g.: SELECT cast('2021-01-09T00:00:00' as smalldatetime) as slot).

Is there a way to do that without using fragments?

#2

sure you can but haven’t tested nested, inner queries but cast and rest can be done.

def update_title(post, new_title) do
  query =
    from "posts",
      where: [id: ^post.id],
      update: [set: [title: ^new_title]]

  MyApp.Repo.update_all(query, [])
end

More examples can be found at

https://hexdocs.pm/ecto/schemaless-queries.html

1 Like
#3

Btw if you are dealing with datetime, please check out these before investing unnecessary time into fragments() like I did

https://hexdocs.pm/ecto/Ecto.Query.API.html#from_now/2

Intervals

Ecto supports following values for interval option: "year", "month", "week", "day", "hour", "minute", "second", "millisecond", and "microsecond".

Date/Time functions like datetime_add/3, date_add/3, from_now/2, ago/2 take interval as an argument.

All of these will execute on the SQL server and use the native underlying functions

4 Likes
#4

Wow thanks for sharing, I had been using Timex for this purpose but built-in solution is great!

#5

Thanks for your reply but it does not answer my question. Perhaps I did not explain correctly.

How would you write SELECT 1 with Ecto? Here I select a simple literal and it is not done over any table.

#6

https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4

Ecto.Adapters.SQL.query(MyRepo, "SELECT $1::integer + $2", [40, 2])
{:ok, %{rows: [[42]], num_rows: 1}}
1 Like
#7

Once again, I did not explain correctly. Sorry for that.

In my initial question, I wrongly wrote “without using fragments”. What I meant was “without using raw SQL”.

#8

I’m not going to answer your question again :smile:

In the sample above, I’d personally opt to “inject” the empty slot once the results have come back from the database. Adding to the front of a list in Elixir is a very “cheap” operation.

e.g.

  slots = [%{slot: ~D[<whatever date>]} | Repo.all(more_basic_query)]

I suspect the situation you are asking about (effectively “UNION”-ing a hand crafted row of data with database results) doesn’t fit within the Ecto model. I had another look through the documentation and I can’t see anything that comes close. So you will either need to use a fragment, or go for the option I noted above, or build a view that performs the UNION on the database side.

2 Likes
#9

No, there currently is no way to use Ecto.Query with no or non-relation FROM (so no stored procedures for you either).

1 Like