Sql and html rendering old school, can help?

[RDP1974]

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

Roberto Della Pasqua - Software e Sistemi

[dimitarvp]

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.

@RDP1974

• how to access low level SQL dataset

Not sure what you mean, clarify please?

@RDP1974

• how to iterate SQL dataset (we don’t have loops in Elixir)

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

@RDP1974

• how to render/pre-compile a html view (or direct PLUG IO stream) with custom HTML inserts

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

@RDP1974

• how to solve the above task with Elixir/Phoenix?

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.

[RDP1974]

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

[dimitarvp]

The Phoenix guide I linked to has plenty. 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.

[RDP1974]

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?

[dimitarvp]

Recursion

Enumerables and Streams

[RDP1974]

Thanks

[emoragaf]

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

[al2o3cr]

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

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.

[RDP1974]

very easy and effective, with nice syntax
thanks