How to query for villas with bedroom air-conditioning?

Questions / Help
, ,
1

This is the Villa:

  Properties.Villa{
  __meta__: #Ecto.Schema.Metadata<:loaded, "villas">,
  id: 1,
  advertised_name: "Villa 1",
  original_name: nil,
  unique_key: "12345",
  description: "A beautiful villa with a pool",
  short_description: "A beautiful villa",
  amenities: %{
    "bathroom" => ["Bathtube"],
    "bedroom" => ["Air Conditioning", "Fan", "TV", "Safety Box",
     "Interconnected Rooms", "Mosquito net", "Build-in wardrobe",
     "Walk-in wardrobe"],
    "kitchen" => ["Fridge", "Oven", "Stove", "Microwave", "Coffee Machine",
     "Water Dispenser"],
    "living_room" => ["Air Conditioning", "Ceiling Fan", "Couch", "TV",
     "Stereo Sound System"],
    "office" => ["Office equipped", "Office available", "Air Condition", "Fan"],
    "outdoor_facilities" => ["Jacuzzi", "Gazebo", "Terrace", "Balcony",
     "Sunbeds", "Outdoor Shower", "Garage", "Parking Lot", "Storage Room",
     "Guest Toilet", "Maids Room", "Security Post"],
    "utilities" => ["Washing Machine", "BBQ", "Sauna",
     "Electricity Generator (genset)", "CCTV Camera System", "Deep Well Water",
     "PDAM Water"]
    },
    property_type: :freehold,
    villa_specification: %PetalPro.Properties.VillaSpecification{
    id: "da96a9dc-7d28-437a-89c2-f11786d7632b",
    number_of_bedrooms: 3,
    number_of_bathrooms: 2,
    number_of_floors: 2,
    land_size_in_sqm: 1000.0,
    building_size_in_sqm: 300.0,
    building_year: 2000,
    garden_size_in_sqm: nil,
    pool_perimeter: nil,
    in_or_outdoor_living_room: nil,
    living_room_type: nil,
    furniture: nil,
    land_certificate: nil,
    building_permit: nil,
    villa_id: nil,
    villa: #Ecto.Association.NotLoaded<association :villa is not loaded>,
    inserted_at: ~N[2023-07-07 04:37:41],
    updated_at: ~N[2023-07-07 04:37:41]
    }
 }

Schema of Villa:

  schema "villas" do
    field :advertised_name, :string
    field :original_name, :string
    field :unique_key, :string

    field :description, :string
    field :short_description, :string

    field :amenities, :map

    field :property_type, Ecto.Enum,
      values: [:freehold, :leasehold, :yearly_rental, :monthly_rental]

    embeds_one :villa_specification, VillaSpecification

    timestamps()
  end

this does not work:

    query =
      from v in Properties.Villa,
        where: fragment("?->>? @> ARRAY[?]", v.amenities, "living_room", ["Ceiling Fan"]),
       # where: fragment("?->>? = ANY(?)", v.amenities, "living_room", ^["Ceiling Fan"]),

        select: v

    PetalPro.Repo.all(query)

this works, as an Enum.filter:

villas = Repo.all(PetalPro.Properties.Villa)

filtered_villas =
  Enum.filter(villas, fn villa ->
    Map.get(villa.amenities, "living_room") |> Enum.member?("Ceiling Fan")
  end)

filtered_villas

how can I get it working with Ecto

2

What error do you see?

Try ? operator

fragment("? \\? ?", v.amenities["living_room"], "Ceiling Fan")
1 Like