Sql and html rendering old school, can help?

hello, I’m newbie about Erlang/Elixir, I found them very cool. I want learn this fantastic toolchain.

I’m a old school coder, I’m using .NET MVC and mainly Delphi (Pascal) for custom web programming.

I have a question, see the below data, I like move to Elixir/Rust/Nif toolchain, however Delphi is fantastic, but about Elixir functional capabilities: how to render a custom table from a sql database, see this code in Delphi/Webbroker:

In the above code I use the HTTP methods routed by webbroker, then with a pair of nested queries I build the html table resulting as the attached image.

The DDL is very basic, just to have a sample.

CREATE TABLE detail_a101addm (
id int NOT NULL AUTO_INCREMENT,
larghezza int DEFAULT NULL,
profondita int DEFAULT NULL,
prezzo float DEFAULT NULL,
welove int DEFAULT ‘0’,
descrizione varchar(2048) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

3 3000 1540 4965 0 8
4 3000 2040 5771 0 8
5 3000 2530 6578 0 8
6 3000 3030 7384 0 8
7 3000 3520 8191 0 8
8 3000 4020 9091 0 8
9 3000 4510 9897 0 8
10 3000 5010 10704 0 8
11 3000 5500 11510 0 8
12 3000 6160 12583 0 8
13 3500 1540 5494 0 8
14 3500 2040 6433 0 8
15 3500 2530 7373 0 8
16 3500 3030 8312 0 8
17 3500 3520 9251 0 8
18 3500 4020 10284 0 8
19 3500 4510 11224 0 8
20 3500 5010 12163 0 8
21 3500 5500 13102 0 8
22 3500 6160 14353 0 8
23 4000 1540 5582 0 8
24 4000 2040 6523 0 8
25 4000 2530 7465 0 8
26 4000 3030 8406 0 8
27 4000 3520 9348 0 8
28 4000 4020 10383 0 8
29 4000 4510 11324 0 8
30 4000 5010 12266 0 8
31 4000 5500 13207 0 8
32 4000 6160 14461 0 8
33 4500 1540 5933 0 8
34 4500 2040 6942 0 8
35 4500 2530 7951 0 8
36 4500 3030 8960 0 8
37 4500 3520 9969 0 8
38 4500 4020 11072 0 8
39 4500 4510 12081 0 8
40 4500 5010 13090 0 8
41 4500 5500 14099 0 8
42 4500 6160 16648 0 9
43 5000 1540 6205 0 8
44 5000 2040 7281 0 8
45 5000 2530 8358 0 8
46 5000 3030 9434 0 8
47 5000 3520 10511 0 8
48 5000 4020 11681 1 8
49 5000 4510 12757 0 8
50 5000 5010 13834 1 8
51 5000 5500 16116 0 9
52 5000 6160 17550 1 9
53 5500 1540 6556 0 8
54 5500 2040 7700 0 8
55 5500 2530 8844 0 8
56 5500 3030 9988 0 8
57 5500 3520 11132 0 8
58 5500 4020 12369 0 8
59 5500 4510 13514 0 8
60 5500 5010 15863 0 9
61 5500 5500 17007 0 9
62 5500 6160 18531 0 9
63 6000 1540 6828 0 8
64 6000 2040 8039 0 8
65 6000 2530 9251 0 8
66 6000 3030 10462 0 8
67 6000 3520 11674 0 8
68 6000 4020 12979 1 8
69 6000 4510 15396 0 9
70 6000 5010 16608 1 9
71 6000 5500 17819 0 9
72 6000 6160 19433 1 9

So:

  • how to access low level SQL dataset
  • how to iterate SQL dataset (we don’t have loops in Elixir)
  • how to render/pre-compile a html view (or direct PLUG IO stream) with custom HTML inserts
  • how to solve the above task with Elixir/Phoenix?

Thank you,
kind regards,
Roberto Della Pasqua
www.dellapasqua.com

Hello, and welcome!

You might need to read up a bit on Ecto, Elixir’s de facto official DB data mapper library. Use myxql instead of postgrex so you can work with your MySQL database.

Not sure what you mean, clarify please?

Repo.all(YourEctoModel) (link to API docs) will give you all records in the table that YourEctoModel points to.

You’ll have to read up on Phoenix, Elixir’s de facto web framework (although there are others as well).

When you have some background both in Phoenix and Ecto, you should be able to solve it pretty easily yourself. If somebody is to post a sample project solving your problem now, that will not be educational.

4 Likes

a pair of hints with source code examples should be useful for newbies as me :slight_smile:

The Phoenix guide I linked to has plenty. :wink: But there are much more Phoenix resources out there – including the official website – and hopefully others will chime in to point at blog posts and other resources.

Atleast can someone suggest a blog or tutorial for who comes from oop languages, I’m often to use highly iterative code; here all is recursive functions. How To do basic things, as having a collection, iterate function, have conditions and branchs?

Thanks

I’m pretty fan of https://elixirschool.com/en/ as a quick resource

So I’ll start with the bad news: one of the idioms in this code isn’t a great fit for how Ecto associates schemas to database tables. In particular, this pattern:

DBQuery.SQL.Text := 'select * from detail_' + Code;

which uses a runtime-generated table name will be tricky to use with the higher-level abstractions. (like assoc etc) On Postgres, Ecto supports the use of a prefix option which handles this kind of multi-tenancy.

BUT NOW, THE GOOD NEWS :tada:

The lower-level Ecto.Query functions are fine working with an interpolated string for the table name, so it’s not a lost cause.

Here’s a sample of how this code could be written in Elixir (not tested, likely contains bugz):

defmodule DetailReport do
  import Ecto.Query

  def price_data_for(code) do
    details =
      raw_query(code)
      |> Repo.all()

    {
      x_values(details),
      y_values(details),
      indexed_map(details)
    }
  end

  defp raw_query(code) do
    from(
      detail in "detail_#{code}",
      select: map(detail, [:larghezza, :profondita, :prezzo, :welove])
    )
  end

  defp x_values(details) do
    MapSet.new(details, & &1.larghezza)
  end

  defp y_values(details) do
    MapSet.new(details, & &1.profondita)
  end

  defp indexed_map(details) do
    Enum.reduce(details, %{}, fn d, results ->
      put_in results, [Access.key(results.profondita, %{}), results.larghezza], d
    end)
  end
end

Then in a Phoenix controller:

  def show(conn, params) do
    {x_values, y_values, details} = DetailReport.price_data_for(params["P"])

    render("show.html", x_values: x_values, y_values: y_values, details: details)
  end

And in the corresponding show.html.eex:

<table border="1" cellspacing="2" cellpadding="2">
  <tr>
    <td>START</td>
    <%= for x <- @x_values do %>
      <td><%= x %></td>
    <% end %>
  </tr>
  <%= for y <- @y_values do %>
    <% row = @details[y] %>
    <tr>
      <td><%= y %></td>
      <%= for x <- @x_values do %>
        <% detail = row[x] %>
        <%= if detail.welove == 1 do %>
          <td style="border: 1px solid red;"><%= detail.prezzo %></td>
        <% else %>
          <td><%= detail.prezzo %></td>
        <% end %>
      <% end %>
    </tr>
  <% end %>
</table>

The template here could be cleaned up with partials and/or content_tag if desired, I left the markup flat to keep it easy to follow.

3 Likes

very easy and effective, with nice syntax
thanks