|> Repo.insert(on_conflict: ProgressTrack.insert_conflict_strategy(progress_data), conflict_target: ) Query: INSERT INTO "progress_track" AS p0 ("user_id","media_item_id","seconds_watched","fully_watched","last_track_timestamp","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8) ON CONFLICT ("seconds_watched","last_track_timestamp") DO UPDATE SET "seconds_watched" = IF last_track_timestamp dbg() ** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "last_track_timestamp" INSERT INTO "progress_track" AS p0 ("user_id","media_item_id","seconds_watched","fully_watched","last_track_timestamp","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8) ON CONFLICT ("seconds_watched","last_track_timestamp") DO UPDATE SET "seconds_watched" = IF last_track_timestamp terminating The full error: QUERY ERROR db=0.0ms queue=12.2ms idle=1608.5ms Any ideas? A further explanation can be found in this youtube video(with timestamp).) Despite that, I’m still getting an error and I’m not sure why. I changed it because the postgres docs seem to show different syntax than the one he is using, found here). (he is using a different IF() syntax than I am. The error is: ** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "last_track_timestamp"īut it’s not clear to me what is going on. The idea is that if the incoming last_track_timestamp is lower than the one in the db, then keep the db version, otherwise, use last_track_timestamp. I’m getting an query error when attempting to apply a conflict strategy here at line 38: progress_track.ex reject ( & &1 = "" ) |> insert_and_get_all ( ) end defp insert_and_get_all ( ) do end defp insert_and_get_all ( names ) do timestamp = NaiveDateTime. put_assoc ( :tags, parse_tags ( params ) ) end # Parse tags has slightly changed defp parse_tags ( params ) do ( params || "" ) |> String. Let's define the schema and the changeset function for a post which may receive tags as a string: defmodule MyApp.Post do use Ecto.Schema schema "posts" do field :title field :body many_to_many :tags, MyApp.Tag, join_through : "posts_tags", on_replace : :delete timestamps ( ) end def changeset ( struct, params \\ % ) do struct |> Ecto.Changeset. In put_assoc/4, we give Ecto structs or changesets instead of parameters, giving us the ability to manipulate the data as we want. When we can't cope with cast_assoc/3, it is time to use put_assoc/4. Again, because the user is simply passing a string, we don't have the ID information at hand. However, here we expect tags to be sent in a string separated by comma.įurthermore, cast_assoc/3 relies on the primary key field for each tag sent in order to decide if it should be inserted, updated or deleted. We can see an example of this in Polymorphic associations with many to many. To do so correctly, Ecto requires tags to be sent as a list of maps. The cast_assoc/3 changeset function was designed to receive external parameters and compare them with the associated data in our structs. While the constraints above sound reasonable, that's exactly what put us in trouble with cast_assoc/3. Once this data is received in the server, we will break it apart into multiple tags and associate them to the post, creating any tag that does not yet exist in the database. Now let's also imagine we want the user to input such tags as a list of words split by comma, such as: "elixir, erlang, ecto". It is important to add an index at the database level instead of using a validation since there is always a chance two tags with the same name would be validated and inserted simultaneously, passing the validation and leading to duplicated entries. Note we added a unique index to the tag name because we don't want to have duplicated tags in our database. Our migrations would look like: create table ( :posts ) do add :title, :string add :body, :text timestamps ( ) end create table ( :tags ) do add :name, :string timestamps ( ) end create unique_index ( :tags, ) create table ( :posts_tags, primary_key : false ) do add :post_id, references ( :posts ) add :tag_id, references ( :tags ) end This is a classic scenario where we would use many_to_many associations. Not only that, a given tag may also belong to many posts. Imagine we are building an application that has blog posts and such posts may have many tags. To showcase those features, we will work on a practical scenario: which is by studying a many to many relationship between posts and tags. In this guide we will learn how to use constraints and upserts. Settings View Source Constraints and Upserts
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |