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

5 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
10

This can be done but its hacky. However, IMO it should not be removed (PLEASE DONT REMOVE THIS we want the query scrubbed without the FROM condition) taking from SQL injection you can use -- to rem out the FROM condition.

MyRepo.all(
      from(d in "_",
        select: %{
          test: fragment("to_timestamp('11:12:02.020.001230', 'HH:MI:SS.MS.US') as slot--")
        }
      )
    )